Solved Code for hiding rows based on multiple column values

May 7, 2012 at 14:46:47
Specs: Windows 7

I'm not sure if this is possible, but I need to write code to hide an entire row if the cells in columns B-F ALL equal either N/A, TBD, or Unknown. Or, to put it another way, if any cell in column B-F has a date, I do NOT want it to be hidden.

For example: I would want rows 1 and 4 below to be hidden automatically (but this is just an example from a much larger worksheet).

11006 N/A N/A 28-Oct-11 N/A 28-Oct-11
11007 N/A N/A N/A N/A 20-Feb-12
11009 N/A N/A TBD TBD Unknown

Is there a code that can do this?

See More: Code for hiding rows based on multiple column values

Report •

May 7, 2012 at 19:11:43
✔ Best Answer
First, please click on the blue line at the bottom of this post and read the instructions on how to post data in this forum.

Then try this code:

Sub HideNoDates()
Dim lastRw, dateFlg, rw, col As Integer
'Determine last row with data in Column A
lastRw = Range("A" & Rows.Count).End(xlUp).Row
'Loop through Rows
 For rw = 1 To lastRw
'Reset Date Flag
  dateFlg = 0
'Loop through Columns B - F for current Row
   For col = 2 To 6
'If a Date is found, set the Date Flag and stop checking
      If IsDate(Cells(rw, col)) Then
       dateFlg = 1
       Exit For
      End If
'If the Date Flag is not set, hide the current Row
  If dateFlg = 0 Then Rows(rw).Hidden = True
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

May 8, 2012 at 07:20:23
Thank you so much. And thank you also for the instructions how to paste information properly. I really appreciate it.

Is there any way to run it so it will unhide the rows and then rehide them each time I run it? Only because, if I change the information so one of the hidden rows has a date, it's not catching the change. It leaves that row hidden.

Report •

May 8, 2012 at 07:30:08
Nevermind. I figured it out.

Thank you again. You have no idea how much this has helped me.

Report •

Related Solutions

Ask Question