Hide/Unhide multiple Excel columns EFFICIENTLY 1

[tmktech]

Hello!

I've got an excel app which gives the user the ability to hide or unhide multiple columns in a 2 dimension data table. Every 3rd column is hidden or unhidden for 75 column occurrences.

I'm using the code below for now, BUT IT TAKES FOREVER!

I think the key is to select multiple columns first and then hide or unhide the selection. The problem is, I can do it manually, but the code captured by the macro recorder doesn't work BECAUSE THERE ARE MERGED CELLS ACROSS THE COLUMNS.

Assume some row has the following cells merged.
A,B,C D,E,F G,H,I J,K,L M,N,O

and I want to hide a column in each merged range.

Here's what the recorder captured:

Range("B:B,E:E,H:H,K:K,N:N&quot.Select
Range("N1&quot.Activate
Selection.EntireColumn.Hidden = True (or FALSE)

This doesn't work when placed in a macro. It selects AND HIDES ALL COUMNS IN THE MERGED RANGE.

I APPRECIATE ANY HELP!!!!!

Thanks!

TMKTECH

--------------- Current Code ------------------

Sub Hide_Show_Cols(ByVal margin_chg As Boolean, ByVal amt_chg As Boolean, ByVal cap_chg As Boolean):

margin_col = [Col1_Margin].Column 'set initial columns
amt_col = [Col1_Amt].Column
cap_col = [Col1_Cap].Column

For c = 1 To 75
If margin_chg Then
Columns(margin_col).Hidden = _
IIf([Show_Margin] = True, False, True)
End If
If amt_chg Then
Columns(amt_col).Hidden = _
IIf([Show_Amt] = True, False, True)
End If
If cap_chg Then
Columns(cap_col).Hidden = _
IIf([Show_Cap] = True, False, True)
End If

margin_col = margin_col + 3 'set up starting columns
amt_col = amt_col + 3
cap_col = cap_col + 3

Next

End Sub

 

[combo]

Hi,
as for hiding all columns - seems that it is due to Selecting. Works:
Range("B:B,E:E,H:H,K:K,N:N&quot.Hidden = True

You execute loop 75 times without using the counter. Assuming that it is necessary for some code not shown here, it should be faster with:
Application.ScreenUpdating=False
For c = 1 To 75
...
Next
Application.ScreenUpdating=True

If you do not need to recalculate worksheet, but change some cells, switch off recalculation while looping (Application.Calculation)

You can also use:
Columns(margin_col).Hidden = ([Show_Margin] = True)

combo

 

[VBAjedi]

Another thing for you to consider:

I didn't learn this until a few months ago, but merged cells are almost never necessary! As long as the cells are just merged for visual/layout purposes, you can get the same effect by putting the information in the leftmost cell, selecting the desired cells, then going to Format/Cells menu. On the Alignment tab, under the Horizontal menu, choose "Center across selection". It looks the same, but doesn't cause problems when selecting, copying, etc. from code.

I haven't needed to merge cells once since I discovered that simple trick!


VBAjedi

 

[tmktech]

Thanks for the two replies, but those won't do the trick.

Combo: I do have screenupdating and calc off when calling the code provided. With respect to the Range(".,.,.&quot.hidden, I get an error when trying to set the property on more than 1 column without selecting first.

VBAjedi: Thanks, but I DO need to merge since these fields are inputs and I can't let the user make an entry into the second or third cell of the "merged" range.

It's interesting that I can manually select multiple columns (that have merged cells across them) and hide them fine. But when I use macro recorder and then execute the code (example above) it hides ALL columns across the merge.

Any more thoughts???

 

[VBAjedi]

Well, the following works in XL97, anyway:

1) Hold down the Ctrl key and select one cell from each column you want to hide. Name this "mygroup" in the names box.

2) Run the following code from a normal module:

Worksheets("Mysheetname").Range("mygroup").EntireColumn.Hidden = True

Let me know if that does the trick for you!

VBAjedi

 

[tmktech]

VBAJedi:

Thanks for the tip. That works great in XP too! Would you be so gracious as to help me improve on that?

Is there a way to define a range object and - using a loop - define the cells in the range? (versus creating a named range)

Thanks again.

TMKTECH

 

[VBAjedi]

Have a look at the Names.Add method. From the help files:

This example defines a new name for the range A13 on Sheet1 in the active workbook.

ActiveWorkbook.Names.Add _
Name:="tempRange", _
RefersTo:="=Sheet1!$A$1:$D$3"

Is that what you want?


VBAjedi

 

[tmktech]

Not quite. But I've come up with the answer. This lets me build it on the fly, and if my table size changes, I just change the loop count.


--------------------------------------------
dim r1, r2 as range
dim column_num as single

column_num = begining column number
set r1 = range(cells(1,column_num))

For i = 1 to 74
column_num = column_num + 3
set r2 = range(cells(1,column_num))
set r1 = union(r1,r2)
next

r1.entirecolumn.hidden=true
------------------------------------------

Thanks for the inspiration and help!!!

 

[tmktech]

By the way, VBAJedi.

Here's a star. The process before was 20 to 30 seconds. It's now 1 to 2!

All the best!

TMKTECH

 

[VBAjedi]

That's the kind of code tweak that pushes my buttons. . . I love seeing dramatic runtime reductions!

Glad I could help. . .

VBAjedi