Microsoft Office Forums - View Single Post

jburg90 · 02-04-2016, 09:28 PM

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

The row it needs to look in is row 5 starting in cell E thru AB

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