Concise Way To Select All Rows Except First?

[PeteCresswell]

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 can't
figure out how to do it.

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

True?

Either way, can somebody shed some light?

 

[Mike H]

Hi,

here's a few ways

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

Lastrow2 = ActiveSheet.UsedRange.Rows.Count

LastRow3 = Cells.Find("*", SearchOrder:=xlByRows,
SearchDirection:=xlPrevious).Row

Mike

 

[Dave Peterson]

Actually, lastrow2 may give the wrong results.

If the .usedrange didn't 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

 

[JLGWhiz]

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]

Thanks Mike, Dave, JLG.

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

Reasoning: I'm *always* starting with row 2.

Here's 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
create
' ACCEPTS: - Fully-qualified path to the spreadsheet in question
' - Pointer to the report the calling routine is working
on
' - 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,
theSS.Workbooks(1))

' --------------------------
' 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
(1,1)
' with the contents of what the report's header text
would have been
' NB: Here's 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
it

22090 .Parent.save
22099 End With

22990 excel_GenericExport_Finalize = True
22991 End If
22999 End If

excel_GenericExport_Finalize_xit:
DebugStackPop
On Error Resume Next
Set myWS = Nothing
Exit Function

excel_GenericExport_Finalize_err:
BugAlert True, ""
Resume excel_GenericExport_Finalize_xit
End Function
===========================================

 

[Dave Peterson]

Ok.

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

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

But it's your choice.

 

[PeteCresswell]

Ok.

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

I'm looking at it the day after and I'm not sure either.... -)


TIme for a re-think.