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
 

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
 

Forum statistics

Threads
1,078,469
Messages
5,340,507
Members
399,380
Latest member
rovius

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top