CODEWITHSUNDEEP
vbaexcel
Pradeep Kesharwani
Pradeep Kesharwani

Reputation: 1478

Select range for all cells below a merged cell in Excel/VBA

I want to select all of the cells directly below a merged cell.

So for example, if cell A3 was merged to H3, then I would want to select cells A4 to H20. I don't want to include the merged cell in the selection, so I can't just use Range("A4:H20").

I want a way how to get a range(A4:H20) using vba. Any help would be much appreciated.

Upvotes: 0

Views: 2691

Answers (1)

BobbitWormJoe
BobbitWormJoe

Reputation: 639

You could use MergeArea to get the number of columns and use this in conjunction with Offset and Resize to define the range. Example:

Sub BelowMerged()

    Dim nRows As Long, nCols As Long
    Dim rFirstCell As Range, rFinal As Range

    nRows = 17
    Set rFirstCell = ActiveSheet.Range("A3")
    nCols = rFirstCell.MergeArea.Columns.Count

    Set rFinal = rFirstCell.MergeArea.Offset(1, 0).Resize(nRows, nCols)
    rFinal.Select

End Sub

Upvotes: 3

Related Questions