Excel - Concise Way To Select All Rows Except First?

Asked By PeteCresswell on 28-Sep-09 08:31 PM
I guess I want to concoct a .Range and then operate on it.

But, short of iterating thorugh rows to find the last row, I cannot
figure out how to do it.

Googled a little, and "ActiveCell.SpecialCells(xlLastCell) seems to
have the right ring to it.


Either way, can somebody shed some light?

Mike H replied on 28-Sep-09 01:35 PM

here is a few ways

Lastrow1 = Cells(Cells.Rows.Count, "A").End(xlUp).Row

Lastrow2 = ActiveSheet.UsedRange.Rows.Count

LastRow3 = Cells.Find("*", SearchOrder:=xlByRows,

Dave Peterson replied on 28-Sep-09 01:58 PM
Actually, lastrow2 may give the wrong results.

If the .usedrange did not start in A1 (or row 1), you could get the wrong answer.

If the only cell that was used was z99, then the usedrange.rows.count would be
equal to 1.

You could use:

dim LastRow2 as long
with activesheet.usedrange
lastrow2 = .rows(.rows.count).row
end with

To the original poster:

You can do the same thing with an arbitrary range (single area???):

Dim myRng as range
dim LastRow as long
set myrng = activesheet.range("x9:z32")
with myrng
lastrow = .rows(.rows.count).row
end with
msgbox myrng.row & vblf & lastrow


Dave Peterson
JLGWhiz replied on 28-Sep-09 02:00 PM
Option 3 is probably the best one to use when a user does not know the
parameters of their used range.  Option 1 is OK if you are only working on a
specific column as your criteria range.  Option 2 is scary since the used
range could begin in row 10 (or any row other than 1) and the integer
returned would be that many rows short of the actual last row.
PeteCresswell replied on 28-Sep-09 08:31 PM
Thanks Mike, Dave, JLG.

I went with option in spite of JLGWhiz's observation.

Reasoning: I am *always* starting with row 2.

Here is the code I wound up with (MS Access VBA):

Private Function excel_GenericExport_Finalize( _
ByVal theXlsPath As
String, _
ByRef theReport As Report,
ByRef theSS As
Excel.Application _
) As Boolean
22000 DebugStackPush mModuleName & ": excel_GenericExport_Finalize"
22001 On Error GoTo excel_GenericExport_Finalize_err

' PURPOSE: To provide one-stop shopping for the
two ...GenericExport... routines
'          when they put the finishing touches on the .XLS they
' ACCEPTS: - Fully-qualified path to the spreadsheet in question
'          - Pointer to the report the calling routine is working
'          - Pointer that we set to the .XLS so the calling
routine can
'            operate on the document if it wants to
' RETURNS: True as long as nothing abended

22002 Dim myWS     As Excel.Worksheet

Dim lastRow  As Long

22010 If SpreadSheetOpen_Existing(theXlsPath, theSS) = True Then
'    --------------------------
'    Remove any default sheets that the user's Excel defaults may
have created

22011    If defaultSheets_Remove(theSS) = True Then
22012       Set myWS = theSS.Workbooks(1).Worksheets(1)
22013       With myWS
'          --------------------------
'          Name only remaining tab per calling report's .Caption

22019          .Name = WorkSheetName_Legal(theReport.Caption,

'          --------------------------
'          Set all cells except header row's to Courier 10

22020          lastRow = .UsedRange.Rows.Count

22030          With .Rows("2:" & lastRow).Font
22031             .Name = "Courier New"
22032             .Size = 10
22039          End With

'          --------------------------
'          Make header row bold

22040          .Rows(1).Font.Bold = True

'          --------------------------
'          Insert a row at the top of the sheet and populate .Cell
'          with the contents of what the report's header text
would have been
'          NB: Here is where the convention requiring
a .txtReportHeader in every
'              report comes in.

22050          .Rows(1).Insert Shift:=xlDown

22060          With .Cells(1, 1)
22061               .Value = Eval(Right$(theReport!
txtReportHeader.ControlSource, Len(theReport!
txtReportHeader.ControlSource) - 1))
22062               .Font.Bold = True
22063               .Font.Size = 16
22069          End With

'          --------------------------
'          Save .XLS so user does not get prompted when they close

22090          .Parent.save
22099       End With

22990       excel_GenericExport_Finalize = True
22991    End If
22999 End If

On Error Resume Next
Set myWS = Nothing
Exit Function
Dave Peterson replied on 28-Sep-09 03:17 PM

But I am not sure why the safer code is more difficult:

with .usedrange
22020            lastRow = .rows(.Rows.Count).row
end with

But it is your choice.
PeteCresswell replied on 29-Sep-09 11:46 PM
I am looking at it the day after and I am not sure either.... -)

TIme for a re-think.