Hey,
I am wondering to solve this problem but couldn't find any solution, the problem is how to find the last column with the help of VBA when there will be merged cells in between.
Thanks.
Hey,
I am wondering to solve this problem but couldn't find any solution, the problem is how to find the last column with the help of VBA when there will be merged cells in between.
Thanks.
This function will give you the number of the last used column in the row specified by the argument R.
Function LastUsedColumn(ByVal R As Long, _
Optional Ws As Worksheet) As Long
Dim Rng As Range ' last used cell's merge area
Dim C As Long
If Ws Is Nothing Then Set Ws = ActiveSheet
With Ws
Set Rng = .Cells(R, .Columns.Count).End(xlToLeft).MergeArea
End With
With Rng
LastUsedColumn = .Column + .Columns.Count - 1
End With
End Function
For example, the function call below will print the address of that cell to the Immediate pane. The row being examined is row #1.
Private Sub Test()
Debug.Print Cells(1, LastUsedColumn(1)).Address
End Sub
The aboive function call doesn't specify the sheet on which the measure is to be taken. It's worthy of mention, however, that you could specify a tab other than the ActiveSheet.