Option Explicit
Sub Test_xlFirstLastRows()
' Target Application: MS Excel
' Demonstration: display first and last non-blank rows in the active sheet
' and one target sheet
Dim SheetName As String
'
' display sheet name and results from xlFirstRow and xlLastRow
' for the active sheet. Since activesheet is assumed if procs are called
' without a passed arguement, use that method here
'
MsgBox "Worksheet name = " & ActiveSheet.Name & vbCrLf & _
"First non-blank row = " & xlFirstRow & vbCrLf & _
"Last non-blank row = " & xlLastRow, vbInformation, _
"Active Sheet Demonstration"
'
' display sheet name and results from xlFirstRow and xlLastRow
' for "Sheet4". Since this is not the active sheet, the sheet must
' be defined via the passed arguement.
'
SheetName = "Sheet4"
MsgBox "Worksheet name = " & SheetName & vbCrLf & _
"First non-blank row = " & xlFirstRow(SheetName) & vbCrLf & _
"Last non-blank row = " & xlLastRow(SheetName), vbInformation, _
"Passed Sheet Name Demonstration"
End Sub
Function xlFirstRow(Optional WorksheetName As String) As Long
' find the first populated row in a worksheet
If WorksheetName = vbNullString Then WorksheetName = ActiveSheet.Name
With Worksheets(WorksheetName)
On Error Resume Next
xlFirstRow = .Cells.Find("*", .Cells(.Cells.Count), xlFormulas, _
xlWhole, xlByRows, xlNext).Row
If Err <> 0 Then xlFirstRow = 0
End With
End Function
Function xlLastRow(Optional WorksheetName As String) As Long
' find the last populated row in a worksheet
If WorksheetName = vbNullString Then WorksheetName = ActiveSheet.Name
With Worksheets(WorksheetName)
On Error Resume Next
xlLastRow = .Cells.Find("*", .Cells(1), xlFormulas, _
xlWhole, xlByRows, xlPrevious).Row
If Err <> 0 Then xlLastRow = 0
End With
End Function
No comments:
Post a Comment