Function to store last row and use in autofill selection

[wayliff]

I need some help with vba. I'm pretty new to vba and trying to learn.

I currently import a chart to an excel spreadsheet everyday. This chart
generally contains more rows every day and need to find a way to find
the last row on column b and use that into an autofill.

Here's what currently goes on...

Sheets("ST Raw Data").Select
Range("A2").Select
ActiveCell.FormulaR1C1 = "=MID(RC[1],5,2)"
Range("A2").Select
Selection.AutoFill Destination:=Range("A2:A430"),
Type:=xlFillDefault
Range("A2:A430").Select
Sheets("EF Raw Data").Select
Range("A2").Select
ActiveCell.FormulaR1C1 = "=MID(RC[1],5,2)"
Selection.AutoFill Destination:=Range("A2:A50"),
Type:=xlFillDefault
Range("A2:A50").Select

A430 and A50 are the current constants I'd like to replace with
variables stored in a function.

Your help appreciated.

Thanks

 

[jtp]

Give this a shot.

Dim lastrow as Integer

Sheets("ST Raw Data").Select
Range("A2").Select
ActiveCell.FormulaR1C1 = "=MID(RC[1],5,2)"

'This will find the last non-empty row
lastrow = Activsheet.Range("B2").End(xlDown).Row
Range("A2").AutoFill Destination:=Range("A2:A" & lastrow),
Type:=xlFillDefault

Do the same with the "EF Raw Data" sheet. Good luck

 

[wayliff]

Thank you so much...that really did the trick.
I actually made a slight modification to your suggestion to avoi
modifying another line...it worked very well