Need method to hide/unhide columns fast

[Switchman]

I have a sheet that every time I activate it, I need to show/hide columns. The code below “BOM_Column_Hide” will do this but it is to slow. I have been trying to do this using an alternative method.. I was thinking about some way to use the “SpecialCells” command. The code “Test_BOM_Column_Hide” does this. I use a numeric “1” to indicate I want to show the column and text “Delete” to indicate I want to hide the column. I also have several columns hard coded to hide/delete. I also will use these cells later after I export the sheet to delete the same columns that are hidden.

The range name “BOM_Hide_Column_Test” = Row 1, columns “A-DF” I have to make two passes through the selection, once for the text entries and once for the formulas to hide the columns. It is a lot faster, in the 2-3 seconds range on my system.

Does anyone have any suggestions on improving it.


Sub Test_BOM_Column_Hide()
Application.ScreenUpdating = False
Application.Goto Reference:="BOM_Hide_Column_Test"
Selection.EntireColumn.Hidden = False
Selection.SpecialCells(xlCellTypeConstants, xlTextValues).Select
Selection.EntireColumn.Hidden = True
Application.Goto Reference:="BOM_Hide_Column_Test"
Selection.SpecialCells(xlCellTypeFormulas, xlTextValues).Select
Selection.EntireColumn.Hidden = True
Application.ScreenUpdating = True
End Sub





Sub BOM_Column_Hide()
Application.ScreenUpdating = False
Const StartCol = 110
Const EndCol = 1
Const HeaderRow = 1
Dim i As Long
Dim Max As Long
Application.ScreenUpdating = False
Application.EnableEvents = False
Max = Application.WorksheetFunction.Max(Range("a:df"))
Range(Cells(HeaderRow, StartCol), Cells(HeaderRow, EndCol)).EntireColumn.Hidden = False
For i = StartCol To EndCol Step -1
If Cells(HeaderRow, i).Value = "Delete" Then
Columns(i).Select
Selection.EntireColumn.Hidden = True
End If
Next i
Application.EnableEvents = True
End Sub

[Simon Lloyd]

Im no expert by far but if all you want to do is hide columns that have Delete at the top then this will do the trick, just place it in the thisworkbook module
Private Sub Workbook_Open()
Dim MyCell
Dim rng As Range
Set rng = Range("A1:IV1")
For Each MyCell In rng
If MyCell = "Delete" Then
MyCell.EntireColumn.Hidden = True
End If
Next
End Sub
Sub uhide()
Dim MyCell
Dim rng As Range
Set rng = Range("A1:IV1")
For Each MyCell In rng
MyCell.EntireColumn.Hidden = False
Next
End Sub
Regards,
Simon

[Bob Phillips]

See if this is any quicker


Sub HideUnhide()
Dim rng As Range
Dim cell As Range

Range("BOM_Hide_Column_Test").EntireColumn.Hidden = False
For Each cell In Range("BOM_Hide_Column_Test")
If cell.Value = "Delete" Then
If rng Is Nothing Then
Set rng = cell
Else
Set rng = Union(rng, cell)
End If
End If
Next cell

rng.parent.activate
If Not rng Is Nothing Then rng.EntireColumn.Hidden = True
End Sub

[Switchman]

xld, your code work very fast. I benched it against my two solutions. I ran each soution 4 time and averaged the times.

My orriginal loop = 18 seconds
My modifed code = 1 second
Your code = 1 second

[Imdabaum]

I am very new to VBE. I normally fiddle with Access. They are redoing our time keeper website where we keep our time and for the next 3 weeks we will be reporting our time manually. I know this is making a lot of work out of nothing... but I like learning.

Is there a way to do this same thing described here, but for rows instead?
I only use 6 rows and want to hide all rows after that.

[Bob Phillips]

Why not just hide them in Format>Row>Hide

[Imdabaum]

Because if I do it through format>Row>Hide it shows the next available rows. I can't ever reach the last row it just keeps going and going.


Correction.... because I am a moron.

I thought I'd try it one more time and there is an end to an excel worksheet at like row 65,000. If there is a kind mod who would remove evidence of my ignorance that would be bliss. otherwise I'll remain here as a tribute to ignorance.

[makako]

does hiding/unhiding calculate the book?, used to have the same problem and i solved it by using the calculation alternative

sub hidunhid
application.calculation = xlmanual
'code
application.calculation = xlautomatic
end sud

is it for my specific case or excel does calculate?

[Switchman]

I got this from one of the guys I work with. Basically, I use an If statement in a column to perform a validity check. If the test condition is false, i put a 0 in the field. If it is true, put a 1 in the field. The use the auto filter to capability to hide the row. The beauty of this is that it is very fast. Hope this helps.

XLD's solution for hiding the columns was cleaner than my solution, so I used it.


Private Sub Worksheet_Activate()
'
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Range("BOM_Filter_Range").Select
Selection.AutoFilter Field:=1, Criteria1:="1", VisibleDropDown:=False
Call HideUnhide
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Range("B6").Select
End Sub






Sub HideUnhide()

Dim rng As Range
Dim cell As Range

Range("BOM_Hide_Column_Test").EntireColumn.Hidden = False
For Each cell In Range("BOM_Hide_Column_Test")
If cell.Value = "Delete" Then
If rng Is Nothing Then
Set rng = cell
Else
Set rng = Union(rng, cell)
End If
End If
Next cell

rng.Parent.Activate
If Not rng Is Nothing Then rng.EntireColumn.Hidden = True


End Sub

[jindon]

try


Sub test()
Dim r As Range, txt As String
With Range("BOM_Hide_Column_Test")
.Cells.EntireColumn.Hidden = False
Again:
For Each r In Range(.Rows(1).SpecialCells(xlCellTypeVisible).Address)
If r.Value = "Delete" Then
txt = txt & "," & r.Address(0,0)
If Len(txt) > 245 Then
Range(Mid(txt,2)).EntireColumn.Hidden = True
txt = "" : GoTo Again
End If
End If
Next
If Len(txt) Then Range(Mid(txt,2)).EntireColumn.Hidden = True
End With
End Sub