Skip to main content
Code Review

Return to Answer

Added error checking to GetLastCell() - excludes errors from cells with data
Source Link
paul bica
  • 1.2k
  • 8
  • 15
'Returns last cell (max row & max col) using an array (fast)
Public Function GetMaxCellGetLastCell(Optional ByVal ws As Worksheet = Nothing) As Range
 Dim usedRnguRng As Range, usedArrayuArr As Variant, r As Long, c As Long
 Dim ubR As Long, ubC As Long, lRow As Long
 If ws Is Nothing Then Set ws = Application.ThisWorkbook.ActiveSheet
 Set usedRnguRng = ws.UsedRange
 usedArrayuArr = usedRnguRng
 If IsEmpty(usedArrayuArr) Then
 Set GetMaxCellGetLastCell = ws.Cells(1, 1): Exit Function
 End If
 If Not IsArray(usedArrayuArr) Then
 Set GetMaxCellGetLastCell = ws.Cells(usedRnguRng.Row, usedRnguRng.Column): Exit Function
 End If
 ubR = UBound(usedArrayuArr, 1): ubC = UBound(usedArrayuArr, 2)
 For r = ubR To 1 Step -1 'finds'----------------------------------------------- last row
 For c = ubC To 1 Step -1
 If Not IsError(uArr(r, c)) Then
 If Len(Trim$(usedArrayuArr(r, c))) > 0 Then
 lRow = r: Exit For
 ExitEnd ForIf
 End If
 Next
 If lRow > 0 Then Exit For
 Next
 If lRow = 0 Then lRow = ubR
 For c = ubC To 1 Step -1 'finds'----------------------------------------------- last col
 For r = lRow To 1 Step -1
 If Not IsError(uArr(r, c)) Then
  If Len(Trim$(usedArrayuArr(r, c))) > 0 Then
 Set GetMaxCellGetLastCell = ws.Cells(lRow + usedRnguRng.Row - 1, c + usedRnguRng.Column - 1)
 Exit Function
 End If
 End If
 Next
 Next
End Function

'Returns last cell (max row & max col) using an array (fast)
Public Function GetMaxCell(Optional ByVal ws As Worksheet = Nothing) As Range
 Dim usedRng As Range, usedArray As Variant, r As Long, c As Long
 Dim ubR As Long, ubC As Long, lRow As Long
 If ws Is Nothing Then Set ws = Application.ThisWorkbook.ActiveSheet
 Set usedRng = ws.UsedRange
 usedArray = usedRng
 If IsEmpty(usedArray) Then
 Set GetMaxCell = ws.Cells(1, 1): Exit Function
 End If
 If Not IsArray(usedArray) Then
 Set GetMaxCell = ws.Cells(usedRng.Row, usedRng.Column): Exit Function
 End If
 ubR = UBound(usedArray, 1) ubC = UBound(usedArray, 2)
 For r = ubR To 1 Step -1 'finds last row
 For c = ubC To 1 Step -1
 If Len(Trim$(usedArray(r, c))) > 0 Then
 lRow = r
 Exit For
 End If
 Next
 If lRow > 0 Then Exit For
 Next
 If lRow = 0 Then lRow = ubR
 For c = ubC To 1 Step -1 'finds last col
 For r = lRow To 1 Step -1
 If Len(Trim$(usedArray(r, c))) > 0 Then
 Set GetMaxCell = ws.Cells(lRow + usedRng.Row - 1, c + usedRng.Column - 1)
 Exit Function
 End If
 Next
 Next
End Function

Public Function GetLastCell(Optional ByVal ws As Worksheet = Nothing) As Range
 Dim uRng As Range, uArr As Variant, r As Long, c As Long
 Dim ubR As Long, ubC As Long, lRow As Long
 If ws Is Nothing Then Set ws = Application.ThisWorkbook.ActiveSheet
 Set uRng = ws.UsedRange
 uArr = uRng
 If IsEmpty(uArr) Then
 Set GetLastCell = ws.Cells(1, 1): Exit Function
 End If
 If Not IsArray(uArr) Then
 Set GetLastCell = ws.Cells(uRng.Row, uRng.Column): Exit Function
 End If
 ubR = UBound(uArr, 1): ubC = UBound(uArr, 2)
 For r = ubR To 1 Step -1 '----------------------------------------------- last row
 For c = ubC To 1 Step -1
 If Not IsError(uArr(r, c)) Then
 If Len(Trim$(uArr(r, c))) > 0 Then
 lRow = r: Exit For
 End If
 End If
 Next
 If lRow > 0 Then Exit For
 Next
 If lRow = 0 Then lRow = ubR
 For c = ubC To 1 Step -1 '----------------------------------------------- last col
 For r = lRow To 1 Step -1
 If Not IsError(uArr(r, c)) Then
  If Len(Trim$(uArr(r, c))) > 0 Then
 Set GetLastCell = ws.Cells(lRow + uRng.Row - 1, c + uRng.Column - 1)
 Exit Function
 End If
 End If
 Next
 Next
End Function
Update with a new function (using Array), for improved performance
Source Link
paul bica
  • 1.2k
  • 8
  • 15

Improved versionversions of GetMaxCell() - thanks @Mat's Mug and @ZigD (SO) for suggestions:

  • It defaults to ActiveSheet.UsedRange if optional parameter is not sent

  • If the range is empty it returns Cell( 1, 1 ) as default, instead of Nothing

    • It requires an extra check for Len( Cell( 1, 1 ).Value2 ) = 0 as a final confirmation
    The first function, using an array is much faster

  • It returns reference to proper range and .Parent

    If called without the optional parameter, will default to .ThisWorkbook.ActiveSheet

  • If the Else branch is entered the range is not empty

    If the range is empty will returns Cell( 1, 1 ) as default, instead of Nothing

GetMaxCell (Array): Duration: 0.0000790063 seconds
GetMaxCell (Find): Duration: 0.0002903480 seconds

.Measured with MicroTimer 

'Returns last cell (max row & max col) using an array (fast)
Public Function GetMaxCell(Optional ByRefByVal rngws As RangeWorksheet = Nothing) As Range
 Dim usedRng As Range, usedArray As Variant, r As Long, c As Long
 'ItDim returnsubR theAs Long, ubC As Long, lRow As Long
 If ws Is Nothing Then Set ws = Application.ThisWorkbook.ActiveSheet
 Set usedRng = ws.UsedRange
 usedArray = usedRng
 If IsEmpty(usedArray) Then
 Set GetMaxCell = ws.Cells(1, 1): Exit Function
 End If
 If Not IsArray(usedArray) Then
 Set GetMaxCell = ws.Cells(usedRng.Row, usedRng.Column): Exit Function
 End If
  ubR = UBound(usedArray, 1)
 ubC = UBound(usedArray, 2)
 For r = ubR To 1 Step -1 'finds last cellrow
 of range with data For c = ubC To 1 Step -1
 If Len(Trim$(usedArray(r, orc))) A1> if0 WorksheetThen
 is empty lRow = r
 Exit For
 End If
 Next
 If lRow > 0 Then Exit For
 Next
 If lRow = 0 Then lRow = ubR
 For c = ubC To 1 Step -1 'finds last col
 For r = lRow To 1 Step -1
 If Len(Trim$(usedArray(r, c))) > 0 Then
 Set GetMaxCell = ws.Cells(lRow + usedRng.Row - 1, c + usedRng.Column - 1)
 Exit Function
 End If
 Next
 Next
End Function

'Returns last cell (max row & max col) using Find
Public Function GetMaxCell2(Optional ByRef rng As Range = Nothing) As Range
 Const NONEMPTY As String = "*"
 Dim lRow As Range, lCol As Range
 If rng Is Nothing Then Set rng = Application.ThisWorkbookActiveWorkbook.ActiveSheet.UsedRange
 If WorksheetFunction.CountA(rng) = 0 Then
 Set GetMaxCellGetMaxCell2 = rng.Parent.Cells(1, 1)
 Else
 With rng
 Set lRow = .Cells.Find(What:=NONEMPTY, LookIn:=xlFormulas, _
 After:=.Cells(1, 1), _
 SearchDirection:=xlPrevious, _
 SearchOrder:=xlByRows)
 If Not lRow Is Nothing Then
 Set lCol = .Cells.Find(What:=NONEMPTY, LookIn:=xlFormulas, _
 After:=.Cells(1, 1), _
 SearchDirection:=xlPrevious, _
 SearchOrder:=xlByColumns)
 Set GetMaxCellGetMaxCell2 = .Parent.Cells(lRow.Row, lCol.Column)
 End If
 End With
 End If
End Function

Private Declare PtrSafe Function getFrequency Lib "Kernel32" Alias "QueryPerformanceFrequency" (cyFrequency As Currency) As Long
Private Declare PtrSafe Function getTickCount Lib "Kernel32" Alias "QueryPerformanceCounter" (cyTickCount As Currency) As Long
'https://msdn.microsoft.com/en-us/library/office/ff700515(v=office.14).aspx#Anchor_5
Function MicroTimer() As Double
 Dim cyTicks1 As Currency
 Static cyFrequency As Currency
 MicroTimer = 0
 If cyFrequency = 0 Then getFrequency cyFrequency 'Get frequency
 getTickCount cyTicks1 'Get ticks
 If cyFrequency Then MicroTimer = cyTicks1 / cyFrequency 'Returns Seconds
End Function

Improved version of GetMaxCell() - thanks @Mat's Mug and @ZigD (SO) for suggestions:

  • It defaults to ActiveSheet.UsedRange if optional parameter is not sent

  • If the range is empty it returns Cell( 1, 1 ) as default, instead of Nothing

    • It requires an extra check for Len( Cell( 1, 1 ).Value2 ) = 0 as a final confirmation

  • It returns reference to proper range and .Parent

  • If the Else branch is entered the range is not empty

Public Function GetMaxCell(Optional ByRef rng As Range = Nothing) As Range
 'It returns the last cell of range with data, or A1 if Worksheet is empty
 Const NONEMPTY As String = "*"
 Dim lRow As Range, lCol As Range
 If rng Is Nothing Then Set rng = Application.ThisWorkbook.ActiveSheet.UsedRange
 If WorksheetFunction.CountA(rng) = 0 Then
 Set GetMaxCell = rng.Parent.Cells(1, 1)
 Else
 With rng
 Set lRow = .Cells.Find(What:=NONEMPTY, LookIn:=xlFormulas, _
 After:=.Cells(1, 1), _
 SearchDirection:=xlPrevious, _
 SearchOrder:=xlByRows)
 Set lCol = .Cells.Find(What:=NONEMPTY, LookIn:=xlFormulas, _
 After:=.Cells(1, 1), _
 SearchDirection:=xlPrevious, _
 SearchOrder:=xlByColumns)
 Set GetMaxCell = .Parent.Cells(lRow.Row, lCol.Column)
 End With
 End If
End Function

Improved versions of GetMaxCell()

  • The first function, using an array is much faster
  • If called without the optional parameter, will default to .ThisWorkbook.ActiveSheet
  • If the range is empty will returns Cell( 1, 1 ) as default, instead of Nothing

GetMaxCell (Array): Duration: 0.0000790063 seconds
GetMaxCell (Find): Duration: 0.0002903480 seconds

.Measured with MicroTimer 

'Returns last cell (max row & max col) using an array (fast)
Public Function GetMaxCell(Optional ByVal ws As Worksheet = Nothing) As Range
 Dim usedRng As Range, usedArray As Variant, r As Long, c As Long
 Dim ubR As Long, ubC As Long, lRow As Long
 If ws Is Nothing Then Set ws = Application.ThisWorkbook.ActiveSheet
 Set usedRng = ws.UsedRange
 usedArray = usedRng
 If IsEmpty(usedArray) Then
 Set GetMaxCell = ws.Cells(1, 1): Exit Function
 End If
 If Not IsArray(usedArray) Then
 Set GetMaxCell = ws.Cells(usedRng.Row, usedRng.Column): Exit Function
 End If
  ubR = UBound(usedArray, 1)
 ubC = UBound(usedArray, 2)
 For r = ubR To 1 Step -1 'finds last row
  For c = ubC To 1 Step -1
 If Len(Trim$(usedArray(r, c))) > 0 Then
  lRow = r
 Exit For
 End If
 Next
 If lRow > 0 Then Exit For
 Next
 If lRow = 0 Then lRow = ubR
 For c = ubC To 1 Step -1 'finds last col
 For r = lRow To 1 Step -1
 If Len(Trim$(usedArray(r, c))) > 0 Then
 Set GetMaxCell = ws.Cells(lRow + usedRng.Row - 1, c + usedRng.Column - 1)
 Exit Function
 End If
 Next
 Next
End Function

'Returns last cell (max row & max col) using Find
Public Function GetMaxCell2(Optional ByRef rng As Range = Nothing) As Range
 Const NONEMPTY As String = "*"
 Dim lRow As Range, lCol As Range
 If rng Is Nothing Then Set rng = Application.ActiveWorkbook.ActiveSheet.UsedRange
 If WorksheetFunction.CountA(rng) = 0 Then
 Set GetMaxCell2 = rng.Parent.Cells(1, 1)
 Else
 With rng
 Set lRow = .Cells.Find(What:=NONEMPTY, LookIn:=xlFormulas, _
 After:=.Cells(1, 1), _
 SearchDirection:=xlPrevious, _
 SearchOrder:=xlByRows)
 If Not lRow Is Nothing Then
 Set lCol = .Cells.Find(What:=NONEMPTY, LookIn:=xlFormulas, _
 After:=.Cells(1, 1), _
 SearchDirection:=xlPrevious, _
 SearchOrder:=xlByColumns)
 Set GetMaxCell2 = .Parent.Cells(lRow.Row, lCol.Column)
 End If
 End With
 End If
End Function

Private Declare PtrSafe Function getFrequency Lib "Kernel32" Alias "QueryPerformanceFrequency" (cyFrequency As Currency) As Long
Private Declare PtrSafe Function getTickCount Lib "Kernel32" Alias "QueryPerformanceCounter" (cyTickCount As Currency) As Long
'https://msdn.microsoft.com/en-us/library/office/ff700515(v=office.14).aspx#Anchor_5
Function MicroTimer() As Double
 Dim cyTicks1 As Currency
 Static cyFrequency As Currency
 MicroTimer = 0
 If cyFrequency = 0 Then getFrequency cyFrequency 'Get frequency
 getTickCount cyTicks1 'Get ticks
 If cyFrequency Then MicroTimer = cyTicks1 / cyFrequency 'Returns Seconds
End Function
replaced http://stackoverflow.com/ with https://stackoverflow.com/
Source Link

Improved version of GetMaxCell() - thanks @Mat's Mug and @ZigD @ZigD (SO) for suggestions:

  • It defaults to ActiveSheet.UsedRange if optional parameter is not sent

  • If the range is empty it returns Cell( 1, 1 ) as default, instead of Nothing

    • It requires an extra check for Len( Cell( 1, 1 ).Value2 ) = 0 as a final confirmation

  • It returns reference to proper range and .Parent

  • If the Else branch is entered the range is not empty

Public Function GetMaxCell(Optional ByRef rng As Range = Nothing) As Range
 'It returns the last cell of range with data, or A1 if Worksheet is empty
 Const NONEMPTY As String = "*"
 Dim lRow As Range, lCol As Range
 If rng Is Nothing Then Set rng = Application.ThisWorkbook.ActiveSheet.UsedRange
 If WorksheetFunction.CountA(rng) = 0 Then
 Set GetMaxCell = rng.Parent.Cells(1, 1)
 Else
 With rng
 Set lRow = .Cells.Find(What:=NONEMPTY, LookIn:=xlFormulas, _
 After:=.Cells(1, 1), _
 SearchDirection:=xlPrevious, _
 SearchOrder:=xlByRows)
 Set lCol = .Cells.Find(What:=NONEMPTY, LookIn:=xlFormulas, _
 After:=.Cells(1, 1), _
 SearchDirection:=xlPrevious, _
 SearchOrder:=xlByColumns)
 Set GetMaxCell = .Parent.Cells(lRow.Row, lCol.Column)
 End With
 End If
End Function

More info on Macro performance slow when page breaks are visible (Microsoft)

Improved version of GetMaxCell() - thanks @Mat's Mug and @ZigD (SO) for suggestions:

  • It defaults to ActiveSheet.UsedRange if optional parameter is not sent

  • If the range is empty it returns Cell( 1, 1 ) as default, instead of Nothing

    • It requires an extra check for Len( Cell( 1, 1 ).Value2 ) = 0 as a final confirmation

  • It returns reference to proper range and .Parent

  • If the Else branch is entered the range is not empty

Public Function GetMaxCell(Optional ByRef rng As Range = Nothing) As Range
 'It returns the last cell of range with data, or A1 if Worksheet is empty
 Const NONEMPTY As String = "*"
 Dim lRow As Range, lCol As Range
 If rng Is Nothing Then Set rng = Application.ThisWorkbook.ActiveSheet.UsedRange
 If WorksheetFunction.CountA(rng) = 0 Then
 Set GetMaxCell = rng.Parent.Cells(1, 1)
 Else
 With rng
 Set lRow = .Cells.Find(What:=NONEMPTY, LookIn:=xlFormulas, _
 After:=.Cells(1, 1), _
 SearchDirection:=xlPrevious, _
 SearchOrder:=xlByRows)
 Set lCol = .Cells.Find(What:=NONEMPTY, LookIn:=xlFormulas, _
 After:=.Cells(1, 1), _
 SearchDirection:=xlPrevious, _
 SearchOrder:=xlByColumns)
 Set GetMaxCell = .Parent.Cells(lRow.Row, lCol.Column)
 End With
 End If
End Function

More info on Macro performance slow when page breaks are visible (Microsoft)

Improved version of GetMaxCell() - thanks @Mat's Mug and @ZigD (SO) for suggestions:

  • It defaults to ActiveSheet.UsedRange if optional parameter is not sent

  • If the range is empty it returns Cell( 1, 1 ) as default, instead of Nothing

    • It requires an extra check for Len( Cell( 1, 1 ).Value2 ) = 0 as a final confirmation

  • It returns reference to proper range and .Parent

  • If the Else branch is entered the range is not empty

Public Function GetMaxCell(Optional ByRef rng As Range = Nothing) As Range
 'It returns the last cell of range with data, or A1 if Worksheet is empty
 Const NONEMPTY As String = "*"
 Dim lRow As Range, lCol As Range
 If rng Is Nothing Then Set rng = Application.ThisWorkbook.ActiveSheet.UsedRange
 If WorksheetFunction.CountA(rng) = 0 Then
 Set GetMaxCell = rng.Parent.Cells(1, 1)
 Else
 With rng
 Set lRow = .Cells.Find(What:=NONEMPTY, LookIn:=xlFormulas, _
 After:=.Cells(1, 1), _
 SearchDirection:=xlPrevious, _
 SearchOrder:=xlByRows)
 Set lCol = .Cells.Find(What:=NONEMPTY, LookIn:=xlFormulas, _
 After:=.Cells(1, 1), _
 SearchDirection:=xlPrevious, _
 SearchOrder:=xlByColumns)
 Set GetMaxCell = .Parent.Cells(lRow.Row, lCol.Column)
 End With
 End If
End Function

More info on Macro performance slow when page breaks are visible (Microsoft)

Replaced default workbook from ActiveWorkbook to ThisWorkbook
Source Link
paul bica
  • 1.2k
  • 8
  • 15
Loading
Post Made Community Wiki by rolfl
added some formatting
Source Link
Malachi
  • 29k
  • 11
  • 86
  • 188
Loading
Removed inner If condition, and reformatted vertical white specing for readability
Source Link
paul bica
  • 1.2k
  • 8
  • 15
Loading
added 56 characters in body
Source Link
paul bica
  • 1.2k
  • 8
  • 15
Loading
Source Link
paul bica
  • 1.2k
  • 8
  • 15
Loading
lang-vb

AltStyle によって変換されたページ (->オリジナル) /