Find Last Column with Merged Cells in Between



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.





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.


Answer the Question

You must create an account to use the forum. Create an Account or Login