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


--
wayliff
------------------------------------------------------------------------
wayliff's Profile: http://www.excelforum.com/member.php...o&userid=29860
View this thread: http://www.excelforum.com/showthread...hreadid=495616

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


--
jtp
------------------------------------------------------------------------
jtp's Profile: http://www.excelforum.com/member.php...o&userid=21132
View this thread: http://www.excelforum.com/showthread...hreadid=495616

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

--
waylif
-----------------------------------------------------------------------
wayliff's Profile: http://www.excelforum.com/member.php...fo&userid=2986
View this thread: http://www.excelforum.com/showthread.php?threadid=49561