Thank you for Visiting my Blog

Sunday, 4 June 2017

How to get Last Column in a Row

There are various methods to find Last Column in a Row . Below listed is few of them:
#First:

'Last Column in Row using "End" method option:

Sub LastColumnInOneRow()

'Find the last used column in a Row: row 1 in this example
    Dim LastCol As Integer


    With ActiveSheet
        LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
    End With


    MsgBox LastCol
End Sub

Note:
#Second:


'Last Column in Row using Columns property
Sub LastColumns_Column_example()

    Dim LastCol As Long

    With ActiveSheet.UsedRange
        LastCol = .Columns(.Columns.Count).Column

    End With
    MsgBox LastCol

End Sub
#Third:
'Last Column  in  a Row using  " SpecialCells" option
Sub SpecialCells_Example_Column()
    Dim LastCol As Integer
    With ActiveSheet

        LastCol = .Range("A1").SpecialCells(xlCellTypeLastCell).Column

    End With
    MsgBox LastCol
End Sub
#Fourth:
'Last Column in a Row  using Find option
Sub Range_Find_Column()
'Finds the last non-blank cell on a sheet/range.
Dim lcol As Integer

    lcol = Cells.Find(What:="*", _
                    After:=Range("A1"), _
                    LookAt:=xlPart, _
                    LookIn:=xlFormulas, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False).Column
   
    MsgBox "Last Row: " & lcol
End Sub
Note: all the places where I  hardcoded the row or column value that can be also parameterized.

No comments:

Post a Comment