AutoFill Macro

scotball

Active Member
Joined
Oct 23, 2008
Messages
375
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.

Code:
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
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
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

Active Member
Joined
Oct 23, 2008
Messages
375
i tried this but it autofilled to row 247 even tho the next empty row was row 23... any ideas?
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Guessing at what your sheet looks like

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

scotball

Active Member
Joined
Oct 23, 2008
Messages
375

ADVERTISEMENT

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

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

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

Watch MrExcel Video

Forum statistics

Threads
1,122,498
Messages
5,596,509
Members
414,073
Latest member
Contilly

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top