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

Bekzii

New Member
Joined
Jun 13, 2020
Messages
2
Office Version
  1. 365
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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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.



 
Upvote 0
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.




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.
 
Upvote 0

Forum statistics

Threads
1,212,932
Messages
6,110,748
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top