Page tree

Welcome to FreeSoftwareServers Confluence Wiki

Skip to end of metadata
Go to start of metadata

https://www.excelcampus.com/vba/find-last-row-column-cell/

https://stackoverflow.com/questions/12796973/function-to-convert-column-number-to-letter/12797190#12797190

https://superuser.com/questions/1414357/clear-cells-to-right-of-selected-cell-in-excel-vba-cant-guess-column-letter-h?noredirect=1#comment2134745_1414357

'''''PUBLICVARS'''''
Public lCol
Public lColLet
Public lRow
Public fBlank
'''''ENDPUBVARS'''''
'''''FINDLAST'''''
Public Sub FindLast()

'Find Last Column in Number Form
    lCol = Cells(1, Columns.Count).End(xlToLeft).Column

'Find Last Column Letter
    lColLet = Split(Cells(1, lCol).Address, "$")(1)

'Find the last non-blank cell in column A(1)
    lRow = Cells(Rows.Count, 1).End(xlUp).Row

'Find the First Blank Cell in Column A
    fBlank = Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
 
End Sub
'''''ENDFINDLAST'''''
'''''FINDLAST'''''
Public Sub FindLast(strSheet)

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets(strSheet)

'Find Last Column in Number Form
    lCol = ws.Cells(1, Columns.Count).End(xlToLeft).Column

'Find Last Column Letter
    lColLet = Split(Cells(1, lCol).Address, "$")(1)

'Find the last non-blank cell in column A(1)
    lRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
    
'Find the First Blank Cell in Column A
    fBlank = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1).Row

End Sub
'''''ENDFINDLAST'''''

.Range("A2:" & Split(Cells(1, Cells(1, Columns.Count).End(xlToLeft).Column).Address, "$")(1) & Cells(Rows.Count, 1).End(xlUp).Row)
("A1:A" & Application.ActiveSheet.UsedRange.Rows.Count)
  • No labels