Determing last (used) row in a sheet
 

Hi,

determing the last (used) row with

Set LastCell = ActiveSheet.Cells.SpecialCells(xlLastCell)
No_Rows = LastCell.Row

seems not to work, as soon I am also deleting some content of the sheet.

Does anybody know what the problem is (or what would be a better solution)?

Kind regards, AXEL

Determing last (used) row in a sheet
 

With code - this has to be one of the most asked and answered queries!!

If you have a list set-up for your data one usually just looks for the last cell with data from the first column - usually A:A. This sets the first empty cell below all data in A column.

Set SRng = Activesheet.Cells(rows.count,1).end(xlup)(2)

If you don't have a list or know where the data might be, or need to know where the first whole blank row is below all data: then..

Dim i As Long, SRng As Range, Rcount As Long
Set SRng = ActiveSheet.UsedRange
Rcount = SRng.Rows.Count
For i = Rcount + SRng.Row To 1 Step -1
If Application.CountA(SRng.Rows(i)) < 0 Then Exit For
Next i
MsgBox "First empty row is " & SRng.Rows(i + 1).Address(0, 0)

.... is all you need :)

Columns can be reduced in a similar way.


Regards
Robert McCurdy

"Axel" wrote in message ...
Hi,

determing the last (used) row with

Set LastCell = ActiveSheet.Cells.SpecialCells(xlLastCell)
No_Rows = LastCell.Row

seems not to work, as soon I am also deleting some content of the sheet.

Does anybody know what the problem is (or what would be a better solution)?

Kind regards, AXEL

Determing last (used) row in a sheet
 

Try this:
No_Rows = Cells.Find(What:="*", _
After:=Range("A1"), _
LookAt:=xlPart, _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
Regards,
Stefi


€žAxel€� ezt Ã*rta:

Hi,

determing the last (used) row with

Set LastCell = ActiveSheet.Cells.SpecialCells(xlLastCell)
No_Rows = LastCell.Row

seems not to work, as soon I am also deleting some content of the sheet.

Does anybody know what the problem is (or what would be a better solution)?

Kind regards, AXEL