Macro to copy a range of merged cells into only rows which are visible

[Bekzii]

Hi All,

I'm new here so apologies in advance if I overstep on the etiquette or for showing my ignorance.

I am really hoping you can help - my specific problem is as follows:

I have text data in merged cells on columns B to D for each row from 200 through to 213 (so 14 rows total).

I would like to copy them over into these target rows 7 to 20 in the same columns B to D, as merged. BUT - only if the target rows are visible rows.

There will be other macros at play that will be simply hiding certain rows within that range of 7 to 20 - hence, for example if the rows 11 to 20 were made hidden, I would like for the code to start copying into rows 7 to 10 then continue onward to row 21 (next visible row) thus, skipping the hidden rows in between.

I would expect the code to look for the next visible row and paste the next row from the source rows (200-213) into it whilst skipping any hidden rows.

Please note, rows 7 through 10 would never be hidden its only 11 through 20. I would also expect that whilst 7 is the 1st row, 108 will be the last.

If any help - this is my attempt so far which clearly doesn't work at all:

Sub VERSIONCONTROL_TAB_HideRows_TestMinus1()

Dim baseMenu As Range, cellBase As Range, cellTarget As Range, targetMenu As Range

Set baseMenu = Range("B200:D213")
Set targetMenu = Range("B7:D108")

For Each cellBase In baseMenu

For Each cellTarget In targetMenu

If Worksheets("TEST").Range("B7:D108").EntireRow.Hidden = False Then

Range("B200:D213").Copy Range("B7:D20")

Else: cell.Offset(1, 1).Value = True

End If

Next

Next

End Sub

 

[alansidman]

VBA does not work with merged cells easily. You need to manipulate them to be unmerged and then remerge them. You would be better served to use "center Across" instead.

Why are people against merge in excel?

Answer: They are one of the worst things and most hated features in MS Excel because they break soo much stuff Lets suppose you merge and center cells C2:F2 Now you cant delete or move any of the columns C, D, E or F Plus they mess up the Ctrl + Alt + Arrow keys shortcuts So the first t...

www.quora.com

Can't enter an array over merged cells | My Spreadsheet Lab

You created a cool array formula and then merged it across several cells. Later, you realize that you need to modify this array formula. When you try to press Control Shift Enter you get this horrible error: You can't enter an array formula over merged cells

www.myspreadsheetlab.com

 

[Bekzii]

VBA does not work with merged cells easily. You need to manipulate them to be unmerged and then remerge them. You would be better served to use "center Across" instead.

Why are people against merge in excel?

Answer: They are one of the worst things and most hated features in MS Excel because they break soo much stuff Lets suppose you merge and center cells C2:F2 Now you cant delete or move any of the columns C, D, E or F Plus they mess up the Ctrl + Alt + Arrow keys shortcuts So the first t...

www.quora.com

Can't enter an array over merged cells | My Spreadsheet Lab

You created a cool array formula and then merged it across several cells. Later, you realize that you need to modify this array formula. When you try to press Control Shift Enter you get this horrible error: You can't enter an array formula over merged cells

www.myspreadsheetlab.com

Thanks Alan,

I didn't know center across existing so thank you. That being said, I am so far able to copy the merged rows and pasted them in the new range without any issue so would be good to understand if / why you think that might help exactly.

I just need a way of copying the right row from source (200-213) into the right row at target (could be anything from 7-108, depending on what rows are hidden).

To explain further for the benefit of other experts who might be wondering:

What I'm trying to do is have a kind of "navigation menu/pane" in column B-D (rows 7-20 when no rows are hidden) which will always appear in the same place "visually" and it'll contain the data that will be copied from the rows 200-213 which will have hyperlinks that will take me to other tabs in Excel etc where a similar view of the menu will be copied etc, to give the feel of navigating through it.

Now, because the menu would appear in the same position (as rows 7-20) and the fact that this would need to happen irrespective of which rows are being hidden between the range 11 - 108 - I need the code to just copy the menu over the right visible rows (skipping those that are hidden) when they change.

The rows will change because I have macros that will hide and unhide specific ranges of rows between 11 - 108 (this is just basically a way of showing and collapsing sections of the page etc).

hope that clarifies.