#1
|
|||
|
|||
Hide Columns VBA
I am trying to mimic a VBA written for hiding ROWS however i need it to also hide columns the same way based on specific cells = to "" for each column.
Here is the code written for the ROWS Can anyone help me write this for columns? Code:
Sub HideBlankRowsAHB() 'This sub un/hides blank rows in AHB Application.ScreenUpdating = False Dim i As Long Dim myWs As Worksheet Dim NewState As VbTriState Dim dat As Variant Dim rws As Range Dim LastRow As Long Set myWs = Sheets("AHB") With myWs.UsedRange LastRow = 35 'LastRow = .Rows.Count - .Row + 1 ' in case used range doesn't start at row 1 dat = .Columns(2).Resize(LastRow, 1) End With NewState = vbUseDefault With myWs For i = 9 To LastRow If dat(i, 1) = "" Then If NewState = vbUseDefault Then NewState = Not .Rows(i).Hidden End If If rws Is Nothing Then Set rws = Cells(i, 1) Else Set rws = Union(rws, Cells(i, 1)) End If End If Next End With Sheet3.Unprotect Password:="coach" 'Unprotect AHB sheet rws.EntireRow.Hidden = NewState 'Reprotect AHB Sheet w/ the right options Sheet3.Protect Password:="coach", AllowFormattingColumns:=True Sheet3.EnableSelection = xlUnlockedCells Application.ScreenUpdating = True End Sub also, efg are merged, hij are merged, nop are merged, qrs are merged, tuv are merged, wxy are merged and z-aa-ab are merged. Not sure if that matters Thank you for your help Last edited by macropod; 02-05-2016 at 04:56 PM. Reason: Added code tags & formatting |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Hide rows in multiple columns based on zero values | Deane | Excel Programming | 19 | 06-23-2015 11:24 PM |
How to Hide/Un-hide a worksheet based on cell on another sheet. | easton11 | Excel Programming | 1 | 06-02-2015 12:07 PM |
VBA Code to Hide columns based on selection on prior sheet | Silver1379 | Excel Programming | 0 | 04-15-2015 08:40 AM |
How to compare 2 columns with other two columns in EXECL 2007? | Learner7 | Excel | 5 | 06-12-2010 09:54 AM |
Conditional Formatting to Hide Rows or Columns? | sczegus | Excel | 0 | 09-26-2006 04:17 PM |