Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding Columns
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding Columns
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 -- Regards, Bill Renaud |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding Columns
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding Columns
Thanks that does the trick
shame its not very fast though... was hoping they would instantly hide rather than take ages. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding Columns
2nd reply for the win thats super quick thanks.
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding Columns
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). -- Regards, Bill Renaud |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding Columns
On Sep 13, 1:52 pm, OssieMac
wrote: 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding Columns
On Sep 13, 2:32 pm, "Bill Renaud"
wrote: 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). -- Regards, Bill Renaud all columns, not sure why it took so long then... |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding Columns
On Sep 13, 2:39 pm, wrote:
On Sep 13, 2:32 pm, "Bill Renaud" wrote: 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). -- Regards, Bill Renaud all columns, not sure why it took so long then... fixed thanks all. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hiding Columns | Excel Discussion (Misc queries) | |||
Hiding Columns | Excel Discussion (Misc queries) | |||
Hiding Columns | Excel Discussion (Misc queries) | |||
Hiding columns | Excel Programming | |||
Not Hiding Columns | Excel Programming |