Need Help Changing the number of lines the Macro Fills Down

[Need Help Please]

Here's my macro it works to fill in blank names between accounts that I need carried down the spreadsheet.

The account names are always in Column A and will always start at cell A24.

The problem is some days the sheet may be 150 lines long and others as much as 200 lines long. How can I make it so it automatically changes based on how long the spreadsheet is. The last line is always a line that says Grand Total. In the spreadsheet I work the macro below the total row was on Row 165. So there will always be a stopper row, I just need to vary the length of the fill down. I think it has something to do with using Offset, but I'm not sure how to make it work. Thanks in advance!

Sub Filldown()
Range("A24:A164").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"
Range("A1").Select
End Sub

 

[VoG]

Try

Sub Filldown()
Dim LR As Long
LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
Range("A24:A" & LR).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
End Sub

 

[DebugGalpin]

I made something very similar for the months I data I had a problem wiht last week. Looks like this

Looping a macro

Sub RunClean1()

'Copies Dates fields down 1st of the month

Range("A8").Select
Selection.Copy
Selection.End(xlDown).Select
Selection.Offset(-1, 0).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste




End Sub
Sub RunClean2()

'Copies Dates fields down 2nd of the month to 2nd last of the month

Range("A8").Select
Selection.End(xlDown).Select
Selection.Copy
Selection.End(xlDown).Select
Selection.Offset(-1, 0).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste

End Sub
Sub RunClean3()

'Copies Dates fields down Last of the month

Range("A8").Select
Selection.End(xlDown).Select
Selection.Copy
Selection.Offset(0, 1).Select
Selection.End(xlDown).Select
Selection.Offset(0, -1).Select
ActiveSheet.Paste
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste

End Sub
Sub Runcleandata()
Dim iCnt As Integer
For iCnt = 1 To Range("H5").Value
Call RunClean2
Next iCnt

End Sub

Sub Dates()

Application.Run ("Runclean1")
Application.Run ("Runcleandata")
Application.Run ("Runclean3")
End Sub

Give it a try it might help.

 

[Need Help Please]

Thank you so much Peter, it worked perfect!!!! I think I'll stick with the shorter code, but I appreciate the quick response DebugGalpin.

 

[Need Help Please]

Is there any way you can tell me if I want to do the exact same thing to Column B that I did to Column A what would I add to the code. I tried added this but it doesn't work. Thanks again!

Range("B24:B" & LR).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"

 

[VoG]

Perhaps

Sub Filldown()
Dim LR As Long
LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
Range("A24:B" & LR).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
End Sub

 

[Need Help Please]

Thanks Again, Worked Perfectly, you make it look so easy!, Thanks.