Including Merged Cells When Making Selection of Unknown Size - Excel VBA

Question

I am trying to make a selection from a spreadsheet via VBA and paste it to a Word doc. There are merged cells in the selection and I want to include them as is. The selection size may change so I am using .Range.End to get to the end of the selection. Problem is, once my code hits a merged cell, it cannot go further and returns only a part of the selection.

How can I modify this? Preferably I want to achieve this without loops.

Dim rngEnd As Range
Set rngEnd = shDevAvail.Range("C8").End(xlDown)

shDevAvail.Range("A6", rngEnd).Copy
wdDoc.Bookmarks("Device_Avail_Chart").Range.Paste

Here is the data that I am trying to select. The blue rows are the merged cells. When I tried to get to the bottom the selection, I can only pull up to the row before the next blue rows.

If column 'B' above does not contain merged cells, you could do a .End on there and then adjust the range to include the first column. This is analogous to how you'd select the contents in the GUI with the keyboard. — Ryan

OSUZorba OSUZorba · Accepted Answer · 2016-04-28T20:38:07

If you have any column that doesn't have merged cells, you can you the range.end method on that column for example:

Dim i as Integer

i = shDevAvail.Range("A6").End(xlDown).Row
shDevAvail.Range("A6", "C" & i).Copy

If every column is have mergers in it, then you can come up from the bottom, assuming there is nothing else under the range.

Dim i As Integer

i = shDevAvail.Range("C400000").End(xlUp).Row
shDevAvail.Range("A6", "C" & i).Copy

Finally you can use i = shDevAvail.UsedRange.Rows.Count again assuming that you don't have anything after the range in question. If you use this method, if you have any leading blank rows you have to add the number of leading blanks to number returned, i.e. if you have 5 blank rows at the top of your sheet, you'd need to do i = shDevAvail.UsedRange.Rows.Count+5.

Including Merged Cells When Making Selection of Unknown Size - Excel VBA

1 Answers