Need to Hide/Unhide Rows and Columns Based on Several Date Ranges

[zlorinc]

Hello,

I am very new to VBA. I am building a sheet to display deadlines (rows) by project (columns). I would like to have it automatically hide the rows and columns based on the date of the deadline. If the deadline is today or 1 week from today the associated rows and columns should be visible, otherwise, I would like to hide them.

My table is A1:N9, with A1 being a blank, row names A2:A9, and column names B1:N1.

Any help would be greatly appreciated.

Thank you!

[p24leclerc]

it would help us a lot if you could attach a workbook with your actual layout. Put some non-sensitive data in it to show us what it looks like. It is not clear to me right now as how you data are arranged, although I can imagine. But who would loose its time to work on an assumption. Right?

[zlorinc]

sample.xlsx

Sure - thanks for the reply!

[p24leclerc]

Look at the attached workbook for 2 macros. One will hide or show columns and rows based on today's date. The other one will unhide all columns and rows so you can work on your table.
Here is the code:

Public Sub HIde_OR_Show()
Dim C_ell As Range, Col_Rng As Range, Proj_is_due As Boolean
Dim Row_Rng As Range
Application.ScreenUpdating = False
'Lets start with the columns
For Each Col_Rng In Range("B1", Cells(1, Columns.Count).End(xlToLeft))
   Proj_is_due = False
   For Each C_ell In Range(Col_Rng.Offset(1), Cells(Rows.Count, Col_Rng.Column).End(xlUp))
      If C_ell >= Int(Now()) And C_ell < Int(Now()) + 7 Then
         Proj_is_due = True
      End If
   Next
   If Proj_is_due Then
      Col_Rng.EntireColumn.Hidden = False
   Else
      Col_Rng.EntireColumn.Hidden = True
   End If
   Proj_is_due = False
Next
'Here we do the rows
For Each Row_Rng In Range("A2", Cells(Rows.Count, 1).End(xlUp))
   Proj_is_due = False
   For Each C_ell In Range(Row_Rng.Offset(0, 1), Cells(Row_Rng.Row, Columns.Count).End(xlToLeft))
      If C_ell >= Int(Now()) And C_ell < Int(Now()) + 7 Then
         Proj_is_due = True
      End If
   Next
   If Proj_is_due Then
      Row_Rng.EntireRow.Hidden = False
   Else
      Row_Rng.EntireRow.Hidden = True
   End If
   Proj_is_due = False
Next
Application.ScreenUpdating = True
End Sub

Public Sub Unhide_All()
Cells.EntireRow.Hidden = False
Cells.EntireColumn.Hidden = False
End Sub

[zlorinc]

Thank you so much - that was very helpful!