This is a very useful function to me, it helps to find out the position of the last row and column in your worksheet, and will also get the column name for calculation and automation. It is also hard to find out and fix the boundary in a worksheet when you are performing automatic calculation and move.

You can refer to my previous “User defined type”, to create your own variable first, so that this function will work properly.

https://wemakeitx.wordpress.com/2017/03/11/excel-vba-how-to-create-user-defined-type/

‘===================================================================================

‘ Find out the LAST row, column, and return the values (row, column & column name)

‘===================================================================================

Function FindLastRowColumn() As RowColumn

Dim infoRowColumn As RowColumn

Dim iCol As Integer

Dim iRow As Long

Dim lastColumn As Integer

Dim lastRow As Long

Dim MBox As Integer

iCol = ActiveSheet.UsedRange.Column – 1 + ActiveSheet.UsedRange.Columns.Count

lastColumn = iCol

iRow = ActiveSheet.UsedRange.Row – 1 + ActiveSheet.UsedRange.Rows.Count

lastRow = iRow

ColumnName = ConvertToLetter(lastColumn)

‘MBox = MsgBox(“Column: ” & LastColumn & “(” & ColumnName & “)” & vbCrLf & “Row: ” & LastRow, vbOKOnly)

infoRowColumn.xRow = lastRow

infoRowColumn.xColumn = lastColumn

infoRowColumn.xColumnName = ColumnName

FindLastRowColumn = infoRowColumn

End Function