Hide Audit Columns

bjw122

New Member
Joined
Oct 3, 2005
Messages
16
I would appreciate some help with this function. The intent is to hide a whole row if a cell in column 'B' is equal to any of the strings in the array. I'm new, so feel free to change anything but I feel like I'm close. . .

Code:
Sub Hide_Audit_Columns()
'
'  If a cell in column 'B' contains any of the audit column names then hide the row.
'

Dim LastRow As Long, RowCnt As Long
Dim ArrCnt As Integer, ForCnt As Integer
Dim rng As Range

Application.ScreenUpdating = False

LastRow = Range("A65536").End(xlUp).Row
arrStr = Array("CREATED_BY", "CREATED_DATE", "SECLAB")
For RowCnt = 1 To LastRow
  Set rng = Range ("B" & RowCnt).MergeArea
  ' Remove any trailing spaces in "B"
  rng.Value = Trim(rng.Value)
  For ArrCnt = LBound(arrStr) To UBound(arrStr)
    If rng.Cells(1,1) = arrStr(ArrCnt) Then
    
    [Code to hide whole row here]

    End If
  Next ArrCnt
Next RowCnt

' Is this next line really necessary?
set rng = Nothing 

Application.ScreenUpdating = True

End Sub
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
One way:

Code:
Sub Hide_Audit_Columns()
Dim LastRow As Long, RowCnt As Long

Application.ScreenUpdating = False

LastRow = Range("A65536").End(xlUp).Row
arrStr = Array("CREATED_BY", "CREATED_DATE", "SECLAB")

For RowCnt = 1 To LastRow
    With Cells(RowCnt, "B")
        ' Remove any trailing spaces in "B"
        .Value = Trim(.Value)
        
        Select Case .Value
            Case Is = "CREATED_BY", "CREATED_DATE", "SECLAB"
                Rows(RowCnt).Hidden = True
            Case Else
                Rows(RowCnt).Hidden = False
        End Select
    End With
Next RowCnt
   
Application.ScreenUpdating = True

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,118,099
Messages
5,570,207
Members
412,308
Latest member
jt145
Top