Hiding Columns

[glimmer23]

I am sure this si very simple but its one of those days.

I am trying to automatically hide columns based on a value/switch I
have the following VB:

Public Sub MyHide()

Application.ScreenUpdating = False

Dim cell As Range
For Each cell In Range("3:3")
If cell.Value = "1" Then
cell.EntireColumn.Hidden = True
End If
Next cell

Application.ScreenUpdating = True

End Sub

Which works - however its very slow as it check every single cell on
row 3 also if I run the macro again with hidden rows no longer
equaling 1 I need it to unhide them.


So basically I am trying to upgrade the above VBA to - Hide all
columns after the first "1" is found in row 3 and if it is hidden and
not a "1" to be unhidden - and if it helps if its not to be hidden it
will be a "0" and it will be everything left of the first 1.

so basically a macro that finds the first 1 on row 3 and hides
everything to the right and unhides everything to the left is exactly
what I need - if you can help me ill be most grateful.

 

[Bill Renaud]

I removed the double quotes around the value of "1", since I don't think
they are needed, unless you actually have them formatted as a string.
When setting up the For statement, use Range("A3"), then resize it
horizontally to the number of columns of data.
Note the shortcut way of determining whether the column should be hidden
or not by placing a boolean expression on the right side of the equals
sign.

Public Sub MyHide()
Dim rngCell As Range
Dim lngNumColumns As Long

Application.ScreenUpdating = False

lngNumColumns = ActiveSheet.UsedRange.Columns.Count

For Each rngCell In Range("A3").Resize(1, lngNumColumns)
With rngCell
.EntireColumn.Hidden = (.Value = 1)
End With
Next rngCell

Application.ScreenUpdating = True
End Sub

 

[Guest]

The following code should work very quickly.

Public Sub MyHide()

'Unhide all columns on worksheet
Cells.EntireColumn.Hidden = False

'Find '1' in row 3
On Error GoTo notFound 'Trap if not found
Rows("3:3").Find(What:="1", _
After:=Cells(3, 1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False).Activate

On Error GoTo 0 'Turn off error trapping

'Hide all columns from found cell to right of worksheet
Range(ActiveCell, Cells(ActiveCell.Row, Columns.Count)) _
.EntireColumn.Hidden = True

GoTo skipNotfound

notFound:
MsgBox "No value of 1 was found in row 3"

skipNotfound:
Range("A3").Select
End Sub

Regards,

OssieMac

 

[glimmer23]

Thanks that does the trick

shame its not very fast though... was hoping they would instantly hide
rather than take ages.

 

[glimmer23]

2nd reply for the win thats super quick thanks.

 

[Bill Renaud]

How large is your worksheet? I tested with 100 columns, and it takes
less than a second. I agree that OssieMac's technique is probably faster
though, since it does not use a For loop (I usually avoid For loops
myself).

 

[glimmer23]

The following code should work very quickly.

Public Sub MyHide()

'Unhide all columns on worksheet
Cells.EntireColumn.Hidden = False

'Find '1' in row 3
On Error GoTo notFound 'Trap if not found
Rows("3:3").Find(What:="1", _
After:=Cells(3, 1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False).Activate

On Error GoTo 0 'Turn off error trapping

'Hide all columns from found cell to right of worksheet
Range(ActiveCell, Cells(ActiveCell.Row, Columns.Count)) _
.EntireColumn.Hidden = True

GoTo skipNotfound

notFound:
MsgBox "No value of 1 was found in row 3"

skipNotfound:
Range("A3").Select
End Sub

Regards,

OssieMac

Ossiemac Just a small problem with it

works fine if you run the macro from that sheet I neglected to mention
I need to run it from a different sheet.

 

[glimmer23]

How large is your worksheet? I tested with 100 columns, and it takes
less than a second. I agree that OssieMac's technique is probably faster
though, since it does not use a For loop (I usually avoid For loops
myself).

all columns, not sure why it took so long then...

 

[glimmer23]

all columns, not sure why it took so long then...

fixed thanks all.