Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Determing n=? using COUNT | Excel Discussion (Misc queries) | |||
Determing Range Criteria | Excel Worksheet Functions | |||
Determing Value From Date | Excel Discussion (Misc queries) | |||
Determing Last Row | Excel Programming | |||
Determing first row in VBA when subtotaling | Excel Programming |