Checking for Dates in VBA

[wakefield101]

I am trying to make a button that says: If Date identified on row Q10:CA10 is less than Date in cell C6, then hide column(s), otherwise don't hide.

Some parameters:
Dates on row Q10:CA10
The Date format is: mmm-yy;@
Formula on cell Q10 is =EOMONTH($C$4,-1) & Formula on cells R10:CA10 --> not sure if this matters but wanted to tell you anyways

Dates in cell C6 is formatted: mmm-yy;@

Please let me know if you can assist with this code, I would greatly appreciate it.

If you can, please let me know if there is anything else that you need from me.

Thank you so very much!

 

[wakefield101]

Also, D6 should actually be D7, however I made that change after you sent me the original code... does not effect the outcome.

 

[Joe4]

It appears that you also changed the row reference of your row of dates, and are actually looking at row 11, not row 10.

If the data you are working with is not structured in the same manner that you described in your question, then you will need to update the VBA code to reflect ALL those differences:

Sub MyRowHideMacro()

    Dim cell As Range
    For Each cell In Range("Q11:CA11")
        If (cell.Value > 0) And (cell.Value < Range("D7").Value) Then
            Columns(cell.Column).EntireColumn.Hidden = True
        Else
            Columns(cell.Column).EntireColumn.Hidden = False
        End If
    Next cell
    
End Sub

 

[wakefield101]

Hi Joe,

My apologies for the initial post identifying the incorrect data. I had multiple models pulled up and I was referencing an older version. Thank you for sticking with me.

The code you last posted works perfectly!

If there was some way to thank you for your help, please let me know.

 

[Rick Rothstein]

Sub MyRowHideMacro()

    Dim cell As Range
    For Each cell In Range("Q11:CA11")
        If (cell.Value > 0) And (cell.Value < Range("D7").Value) Then
            Columns(cell.Column).EntireColumn.Hidden = True
        Else
            Columns(cell.Column).EntireColumn.Hidden = False
        End If
    Next cell
    
End Sub

Here is a slightly more compact way to write this macro...

Sub MyRowHideMacro()
  Dim Cell As Range
  For Each Cell In Range("Q11:CA11")
    Cell.EntireColumn.Hidden = Abs(Cell.Value - [D7/2]) < [D7/2]
  Next Cell
End Sub

 

[Joe4]

If there was some way to thank you for your help, please let me know.

You just did!

We do not allow any sort of compensation or anything like that, so do not worry about that.