Number of rows and columns in the active spreadsheet

To find the last row and last column of a spreadsheet and set the variables NUMROWS and NUMCOLS can be accomplished with the following code.

    CURROW = ActiveCell.Row                       
    CURCOL = ActiveCell.Column
    Selection.SpecialCells(xlLastCell).Activate 'select the cell in the last row and column 
    NUMROWS = ActiveCell.Row
    NUMCOLS = ActiveCell.Column
    Cells(CURROW, CURCOL).Select                'Reset the selected cell to before the lastcell
 

Excel remembers the lastcell in the spreadsheet and deleting rows or colunns does not change the lastcell. To get the last non blank cell in a specified column and the last non blank cell in a specified row with the following functions. Since both functions start with the active cell to select the row or column the correct cell should be selected before execution. This code can reduce the execution time of the procedure.

VBA Fifth Dimension Macro-GET_NUMROWS and GET_NUMCOLS

Review the following code, copy it to Excel and execute it.

Function GET_NUMROWS()
   'Get the last non blank cell in column
    CURROW = ActiveCell.Row
    CURCOL = ActiveCell.Column
    Selection.SpecialCells(xlLastCell).Activate
    GET_NUMROWS = ActiveCell.Row
    NUM_ROWS = GET_NUMROWS
    Cells(CURROW, CURCOL).Select
    For KK = 1 To NUM_ROWS
        II = NUM_ROWS - KK + 1
        If IsEmpty(Cells(II, CURCOL)) = False Then
            GET_NUMROWS = II
            Exit Function
        End If
    Next
    GET_NUMROWS = 0
End Function
Function GET_NUMCOLS()
   'Get last non blank cell in row
    SUBNAME = "GET_LAST_NON_BLANK_COLUMN"
    CURROW = ActiveCell.Row
    CURCOL = ActiveCell.Column
    Selection.SpecialCells(xlLastCell).Activate
    NUM_COLS = ActiveCell.Column
    GET_NUMCOLS = NUM_COLS
    Cells(CURROW, CURCOL).Select
    For KK = 1 To NUM_COLS
        JJ = NUM_COLS - KK + 1
        If IsEmpty(Cells(CURROW, JJ)) = False Then
            GET_NUMCOLS = JJ
            Exit Function
        End If
    Next
    GET_NUMCOLS = 0
End Function