A combination of 2 functions in one

Question

i wrote this 2 functions which returns values of last row and column in worksheet. They are same but returns difrent values. Is it posible to combine them to 1 function in simple way? i think about global variable i_lRow and i_lColumn and assign value into function or do some class for example wsSetting and add properties named lastRow, lastColumn.

Sub testWywolania()
variable = GetMaxLastColumn(ActiveSheet)
variable2 = GetMaxLastRow(ActiveSheet)
MsgBox variable & vbCrLf & variable2
End Sub

Function GetMaxLastRow(ws As Worksheet) As Integer
Dim i_lr As Integer, i_lc As Integer, i_tempLc As Integer, i_assumption As Integer, i_assumptionR As Integer, i_assumptionC As Integer, i_aVal As Integer
Dim r_workRange As Range

i_lc = 0
i_lr = 0
i_assumption = 30
i_aVal = i_assumption
i_assumptionC = i_assumption
i_assumptionR = i_assumption

Do
    i = i + 1
    Do
        j = j + 1
        i_tempLc = ActiveSheet.Cells(i, Columns.Count).End(xlToLeft).Column
        i_tempLr = ActiveSheet.Cells(Rows.Count, j).End(xlUp).Row
        If i_tempLc > i_lc Then i_lc = i_tempLc:   i_tempLc = 0
        If i_tempLr > i_lr Then i_lr = i_tempLr: i_tempLr = 0
        If i >= i_aVal And j >= i_aVal Then
            i_assumptionR = i_lr
            i_assumptionC = i_lc
        End If
    Loop While j <= i_assumptionC
Loop While i <= i_assumptionR

GetMaxLastRow = i_lr
End Function

Function GetMaxLastColumn(ws As Worksheet) As Integer
Dim i_lr As Integer, i_lc As Integer, i_tempLc As Integer, i_assumption As Integer, i_assumptionR As Integer, i_assumptionC As Integer, i_aVal As Integer
Dim r_workRange As Range

i_lc = 0
i_lr = 0
i_assumption = 30
i_aVal = i_assumption
i_assumptionC = i_assumption
i_assumptionR = i_assumption

Do
    i = i + 1
    Do
        j = j + 1
        i_tempLc = ActiveSheet.Cells(i, Columns.Count).End(xlToLeft).Column
        i_tempLr = ActiveSheet.Cells(Rows.Count, j).End(xlUp).Row
        If i_tempLc > i_lc Then i_lc = i_tempLc:   i_tempLc = 0
        If i_tempLr > i_lr Then i_lr = i_tempLr: i_tempLr = 0
        If i >= i_aVal And j >= i_aVal Then
            i_assumptionR = i_lr
            i_assumptionC = i_lc
        End If
    Loop While j <= i_assumptionC
Loop While i <= i_assumptionR

GetMaxLastColumn = i_lc
End Function

user_4996248 · Answer

The intention of your code isn't completely clear, but it seems to be trying to find the used range of a worksheet (with i_assumption = 30 an unexplained number designed to limit the manual search for the bounds of this range so that the runtime is acceptable). If so, you could just drop your two functions entirely and directly use the UsedRange property of worksheet objects. The following gives the same output as your test sub:

Sub test()
    Dim variable As Long, variable2 As Long
    Dim R As Range
    Set R = ActiveSheet.UsedRange
    variable = R.Cells(1, 1).Column + R.Columns.Count - 1
    variable2 = R.Cells(1, 2).Row + R.Rows.Count - 1
    MsgBox variable & vbCrLf & variable2
End Sub

Beyond that, your code has several issues:

You pass ws but then never use it but instead revert to ActiveSheet in the body of the functions.
You are using Integer to stand for row and column numbers -- but that risks overflow. Just use Long.
While you declare some variables, you don't declare others. That is inconsistent and potentially a source of bugs. Using Option Explicit is a good way to force yourself to be more consistent. In fact, you can do it once and for all by using the "Require Variable Declaration" option in the VBA Editor Options (under Tools/Options).

user_9814069 · Answer

Last Row and Last Column

Option Explicit

Function calcLastRC( _
    ws As Worksheet, _
    Optional ByVal doColumn As Boolean = False) _
As Long
    ' A reminder that it may fail when the worksheet is filtered.
    ' Hidden rows are covered with `xlFormulas`.
    'If ws.AutoFilterMode Then
    '    ws.AutoFilterMode = False
    'End If
    Dim rg As Range
    If doColumn Then
        Set rg = ws.Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious)
        If Not rg Is Nothing Then
            calcLastRC = rg.Column
        End If
    Else
        Set rg = ws.Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious)
        If Not rg Is Nothing Then
            calcLastRC = rg.Row
        End If
    End If
End Function

Sub TestcalcLastRC()
    Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Sheet1")
    Debug.Print "Last Row:    " & calcLastRC(ws)
    Debug.Print "Last Column: " & calcLastRC(ws, True)
End Sub

user_14624798 · Answer

ws.usedrange.columns & rows.count returns 5,5 i think is table surface.

code take usedrange from this worksheet

Sub test()
Dim wss As Worksheet
Set wss = ThisWorkbook.Worksheets("wsTest")
MsgBox wss.UsedRange.Columns.Count & vbCrLf & wss.UsedRange.Rows.Count


End Sub

A combination of 2 functions in one

3 Answers

Last Row and Last Column