Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Find Last Column with Merged Cells in Between

0

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.

Answer
Discuss

Answers

0

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.

Discuss


Answer the Question

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