Selecting multiple columns with merged cells

[maw230]

I need to write a simple function to hide several separate column sets. I can do this fine manually by selecting the sets (e.g. select Columns D:F -> hold ctrl -> select Columns H:J, etc) and then hiding them that way. However, when I attempt to select these using a macro it will select the entire range that has a merged cell as a title. For example, the range of columns D:L has a title cell called "Margin" that spans the entire range. This seems to be throwing off my macro for selecting separate column ranges.

How can I fix this?

[Mordred]

One thing that I learned very early on was that merged cells are horrific to write code around. I would suggest removing your merged cells, you'll be happy you did. The person that I write code for here at work has set up many of his workbooks with merged cells as headers and what not! I basically redesigned (still am) his workbooks so that I can write meaningful and efficient (to my best abilities) code for our processes and requirements.

[maw230]

One thing that I learned very early on was that merged cells are horrific to write code around. I would suggest removing your merged cells, you'll be happy you did. The person that I write code for here at work has set up many of his workbooks with merged cells as headers and what not! I basically redesigned (still am) his workbooks so that I can write meaningful and efficient (to my best abilities) code for our processes and requirements.

Good to know. I suppose I could just fill those cells with white and type in a title... except when I hide a range of cells if will no longer be centered...

[Mordred]

Good to know. I suppose I could just fill those cells with white and type in a title... except when I hide a range of cells if will no longer be centered...

Yeah, I had the same issues regarding layout formats after I removed the merged cells but the users get used to the changes when they have an automation that works really well. Are the workbooks that you are working on for you or for others? If they are for others, be sure to let them know of any layout changes you make otherwise they get confused and grumpy!

[maw230]

Are the workbooks that you are working on for you or for others? If they are for others, be sure to let them know of any layout changes you make otherwise they get confused and grumpy!

Yes, but luckily as of now it's just going back and forth between my boss and I until the formatting is straightened out. Then it will be sent out to everyone else.

Basically, I need a macro that will hide certain cells and then re-align the title text box if those cells are hidden.

Is there a built in way to check for hidden columns in VBA?

[Mordred]

Here is a very generic way to check for hidden columns. Important to note is that it is only asking if column B is hidden. It would need to be amended to your criteria.

Please Login or Register  to view this content.

[maw230]

Here is a very generic way to check for hidden columns. Important to note is that it is only asking if column B is hidden. It would need to be amended to your criteria.

Please Login or Register  to view this content.

So, Cells(2,2) refers to cell B2 correct?

[Mordred]

So, Cells(2,2) refers to cell B2 correct?

Yes that is correct.

[maw230]

Thanks for the help. The very generic way works perfectly!

[Mordred]

Hi maw, you are most welcome! If you are satisfied with the answer(s) provide, don't forget to mark your thread as solved. Thanks!

[maw230]

I've been trying to figure this out for the better part of 10 minutes..

[Mordred]

No worries, I'll walk you through it.

1. Go to your first post and click "Edit" (located at the bottom right of your post)
2. Click "Go Advanced"
3. At the top of the message box you'll see your title, just to the left of that there is a dropdown menu that will currently say: (No Prefix), select the menu and then select "Solved"
4. Click "Save Changes" located at the bottom of the message box.