Macro to fill one row based on the last cell used in the previous row | VBA & Macros

Macro to fill one row based on the last cell used in the previous row

Brenda Delorme

Member

Members

Forum Posts: 3

Member Since:
June 16, 2015

Offline

July 1, 2016 - 9:31 am

Scenerio:

Col M has data in Rows 6-10.

I would like to take a value from Col. N, row 5 and paste it into rows 6-10 of the same column (N).

Using autofill increments the value from N5 through 6-10, which I don't want and my existing recorded macro pastes the value into more empty rows than I even want to bother counting. 🙂

Is there a line or two of code that would say - paste the copied value into Col. N for as many rows are used in Col. M?

Unfortunately this file is massive, even when I crop it down to almost nothing so the best I can do is provide a screen shot.

Hopefully it'll help with making sense of what I'm trying to do.

I would appreciate any help,

Thanks, Brenda

See Answer

Catalin Bombea

Iasi, Romania

Admin

Forum Posts: 403

Member Since:
November 8, 2013

Offline

July 1, 2016 - 1:40 pm

Hi Brenda,

You have no attachment, not even an image. Press the Start Upload button after you Add a file, only then the file will be uploaded.

I think you might have unnecessary formattings, this is one of the reasons why the size of a file is unnusually large. You can also paste a sample of your data into a new clean workbook, so we can see what you need.

Cheers,

Catalin

Answers Post

Brenda Delorme

Member

Members

Forum Posts: 3

Member Since:
June 16, 2015

Offline

July 1, 2016 - 3:05 pm

Hi, Catalin

Thanks for the reply! It seems like I have been here forever but I did manage to get an alternative -

Range("N5:N" & LastCell).Value = 1

As for the size of my file- thanks for the suggestion. I thought I had indeed previously tried as you suggested, unsuccessfully, but I did it again and BOOM. From 20,695kb down to 2546.

As always, MOTH Rocks!

Stay safe and well.

And thanks again, Brenda Kiss

tbeards

New Member

Members

Forum Posts: 1

Member Since:
June 27, 2016

Offline

July 7, 2016 - 2:25 am

I see you found an alternative; however, I just wanted to post some code that I use all the time to fill a column based on contents of another column.

Range("N5").Select
Range("N5").Copy Destination:=Range("N5:N" & Cells(Rows.Count, "M").End(xlUp).Row)

Going further, I mostly use the code above to copy a formula down such as:

    Range("C2").Value = "=IF(ISNUMBER(A2),TEXT(A2,""000000""),A2)" 'formula I want to apply to column A to zero fill if it is a number
    Range("C2").Select
    Range("C2").Copy Destination:=Range("C2:C" & Cells(Rows.Count, "A").End(xlUp).Row) 'copy cell C2 down for as many rows are populated in column A
    Range("C:C").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False 'copy and paste the formulas as values
    Range("C:C").Select
    Selection.Copy
    Range("A1").Select
    ActiveSheet.Paste 'move the new values from column C to column A
    Range("A1").Value = "Level 1" 'rename the header to the original header