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

0
votes

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.

1
vbaexcel
Is row 20 the actual limit you would never go past? If so, then BobbitWormJoe's solution should do exactly what you need; else, you could easily update his bit of code to fit your criteria. – Michael Harvey
Actually in my excel sheet there is only 20 rows but it can be higher no of rows – Pradeep Kesharwani

1 Answers

2
votes

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