vba get the first and last column from range code example

Example 1: excel vba find last column

'VBA to get the last column in row 10 that has a value.

'Change `10` to the row you care about:
With Sheet1
    LastCol = .Cells(10, .Columns.Count).End(xlToLeft).Column
End With


'A shorter way:
LastCol = Sheet1.[XFD10].End(xlToLeft).Column


'And to get the column of the last cell with a value
'just to the left of the FIRST blank cell HORIZONTALLY:
LastCol = Sheet1.[A10].End(xlToRight).Column

'----------------------------------------------------------


'The most reliable way to get the last column with data 
'for the entire sheet is to use the following function: 

LastCol = LastUsedRow(Sheet1.[A1])

Function LastUsedCol(r As Range)
    On Error Resume Next
    r.Parent.AutoFilterMode = False
    With r.Cells.Find("*", r, xlFormulas, , , xlPrevious)
        LastUsedCol = 1
        LastUsedCol = .Column
    End With
End Function

Example 2: excel vba last column in range

Dim rRange As Range
Dim rDuplicate As Range

Set rRange = ActiveSheet.UsedRange
lFirstRow = rRange.Row
lLastRow = rRange.Rows.Count + rRange.Row - 1
lFirstColumn = rRange.Column
lLastColumn = rRange.Columns.Count + rRange.Column - 1
' New reference to current range (can be truncated)
Set rDuplicate = Range(Cells(lFirstRow, lFirstColumn), _
      Cells(lLastRow, lLastColumn))