AutoFill Macro

[scotball]

Hi

I am looking for some help here with the final part of my macro.

Basically i want to autofill a value in a column which I can do by recording a macro but the problem is that the number of rows will vary from account to account.

For the example autofill the number of rows was 21 but the number of rows will vary per account.

Sub AutoFillNotification()
'
' AutoFillNotification Macro
'
'
    Selection.AutoFill Destination:=Range("C2:C22")
    Range("C2:C22").Select
End Sub

How can i do the same autofill but where it stops at the first blank row?

Thanks
Lee

 

[VoG]

Try

Sub AFill()
Dim LR As Long
LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
Range("C2").AutoFill Destination:=Range("C2:C" & LR)
End Sub

 

[scotball]

i tried this but it autofilled to row 247 even tho the next empty row was row 23... any ideas?

 

[VoG]

Guessing at what your sheet looks like

Sub AFill()
Dim LR As Long
LR = Range("C2").End(xlDown).Row - 1
Range("C2").AutoFill Destination:=Range("C2:C" & LR)
End Sub

 

[scotball]

Hey VoG,

Sheet is basic, 5 columns and need to autofill column C & E. A = Country, B = Email. D uses nested if statements to populate the info.

It doesnt have to be a blank row it could be the first blank cell in col A...

When i added that 2nd piece of code it autofilled to the 65Kth row... :S

 

[VoG]

Try

Sub AFill()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("C2").AutoFill Destination:=Range("C2:C" & LR)
End Sub

 

[scotball]

Works a total treat sir, many thanks