Last Row Autofill - Slight Twist

[aeddipa]

Hello,

I have code where I am looking to autofill a selected range to the last row of data. I generally know how to build the code correctly, but I have a problem with the beginning of the range.

Range("E2:F2").Select
Selection.PasteSpecial xlPasteValues
LR = Range("A" & Rows.Count).End(xlUp).Row
Selection.AutoFill Destination:=Range("E:F" & LR)

The first line of code was written so people can follow, but it will change as it is a loop macro and I have a relative reference. The issue in the code is obviously the last line. Generally it would be the following:

Selection.AutoFill Destination:=Range("E2:F" & LR)

where an actual row is given for the first set of range. However, since this row number can always change, how can I build it to accomodate my needs? The columns will always be E & F.

Don't worry about Column A as LR. I need it to be that way.

Any help would be greatly appreciated!

 

[rjwebgraphix]

Are you saying that the first row won't always be E2? How is it variable? From nothing at all in column E or just varies between row 2 to row 10? There's lots of variables.

I'm throwing this out as just a question to see if it will work for you.... Can you use the last row - 1 with an end up to determine the first in column E? It was just a thought, totally untested to see if you can.

Range("E2:F2").Select
Selection.PasteSpecial xlPasteValues
lr = Range("A" & Rows.Count).End(xlUp).Row
fr = Range("E" & lr - 1).End(xlUp).Row
Selection.AutoFill Destination:=Range("E" & fr & ":F" & lr)

 

[aeddipa]

That worked incredibly well. Yes, column E does change each time and that is why there is the unknown row number. You illustrated my request perfectly! My only issue is that the data that is in Column E is currently "General" formatting but has numbers. Example: 5712. Due to it not being a number, when autofill it goes 5713, 5714, etc... I need the number to remain the same throughout each autofill row. I tried formatting to number, but the only way to correct it is by then clicking F2 and enter to refresh the cell. Is there a way to refresh the cell after formatting to Number?

ActiveCell.NumberFormat = "0"

Are you saying that the first row won't always be E2? How is it variable? From nothing at all in column E or just varies between row 2 to row 10? There's lots of variables.

I'm throwing this out as just a question to see if it will work for you.... Can you use the last row - 1 with an end up to determine the first in column E? It was just a thought, totally untested to see if you can.

Range("E2:F2").Select
Selection.PasteSpecial xlPasteValues
lr = Range("A" & Rows.Count).End(xlUp).Row
fr = Range("E" & lr - 1).End(xlUp).Row
Selection.AutoFill Destination:=Range("E" & fr & ":F" & lr)