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
'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:
Dim LastCol As Long
With ActiveSheet.UsedRange
LastCol = .Columns(.Columns.Count).Column
End With
MsgBox LastCol
End Sub
LastCol = .Range("A1").SpecialCells(xlCellTypeLastCell).Column
End With
MsgBox LastCol
End Sub
lcol = Cells.Find(What:="*", _
After:=Range("A1"), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
MsgBox "Last Row: " & lcol
'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
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.
'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