Switchman
10-17-2006, 03:51 PM
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
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