VBA code to identify last row in a sheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
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
 
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)
 
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...
 
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.
 
Try the following for finding the last row:

mLastRow=Activesheet.Cells.SpecialCells(xlCellTypeLastCell).Row

You should then be able to use this as below, for example to put the sum of the cells in column E on Sheet1 into cell D3:

range("D3").value="=sum(Sheet1!E1:E"+trim(str(mLastRow)+")"

Good Luck!
 
Back
Top