VBA code to identify last row in a sheet

S

SharonP.

Can someone suggest simple (I'm a beginner) VBA code to identify the last
used row in a particular sheet? Then, once the last used row is identified,
use that variable to extend formulas on different sheets from row A2 to make
the used row number?

I'm working with a spreadsheet that is so large, I'm trying to fit the
needed rows with the input data which changes constantly.

Thanks!
 
D

Dave Peterson

I like to pick out a column that I know has data in it:

dim LastRow as Long
With worksheets("sheet1")
lastrow = .cells(.rows.count,"x").end(xlup).row
.range("a3:A" & lastrow).formular1c1 = .range("a2").formular1c1
end with
 
B

Bob Phillips

iLastRow = Cells(Rows.Count,"A").End(xlUp).Row
With Range("A2")
.Autofill .resize(iLastRow - 1)
End With

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
 
S

SharonP.

I'll give that a shot and let you know. Thanks!

Dave Peterson said:
I like to pick out a column that I know has data in it:

dim LastRow as Long
With worksheets("sheet1")
lastrow = .cells(.rows.count,"x").end(xlup).row
.range("a3:A" & lastrow).formular1c1 = .range("a2").formular1c1
end with
Click to expand...
 
S

SharonP.

I'm very new at VBA and am unable to get this right. I've duplicated
the code and can run it but the result isn't expected. My sample
has two sheets - sheet1 and sheet2. I want to identify the last populated
row of data on sheet1. Then, I want to extend the formulas from sheet2 -
A1, B1, C1, D1 to the appropriate number of rows identified from sheet1.

What happens now with the sample code is the the autofill fills in the cell
contents on sheet1 A1 to the bottom of Sheet1 column A.
 
Top