Hide column if cell date

[GuardianZer0]

Hello, I'm new to this forum and although i've been searching for days for a way to solve my question, I have been unable to do that so far, thus decided to create a new thread.

So.. here is my dilema: I have to record the schedule of workers every month to proceed to payment accordingly. However, where i work, we pay from 21st of given month to the 20th of the next month (21st january to 20th february, 21st february to 20th march, etc). I've managed to automate the table to only insert the current year and month, and it automatically creates a calendar with dates and weekends highlighted.
The problem is, for example, when i select the period from 21st february to 20th march, the table also shows 21st, 22nd and 23rd of march, and I'd like to learn how to hide them.

Can someone help me find a way to solve this issue? I'd be appreciated ^^

 

[ExcelGuyDude]

What VBA code do you have thus far? It is probably easier to add something to your existing code rather than create an entirely new macro.

 

[GuardianZer0]

Well, i'm new to excell as well, and even tho i managed to understand how things work by watching tutorials on how to build calendars to make my very own table, the macros they presented were just to copy paste them and it would work (for their calendars..)

I think this one is the closest to what i required:

Sub Hide_Day()
Dim Num_Col As Long
For Num_Col = 30 To 32
If Month(Cells(6, Num_Col)) <> Cells(1, 1) Then
Columns(Num_Col).Hidden = True
Else
Columns(Num_Col).Hidden = False
End If
Next
End Sub

And this is a print of my current state:

 

[ExcelGuyDude]

ah, ta bom. Entao, faz como assim:

Sub Hide_Day()
    Dim LastDate As Date, Num_Col As Integer
    LastDate = DateAdd("m", 1, Range("D18") - 1)
    For Num_Col = 29 To 35
        If Cells(18, Num_Col) > LastDate Then
            Columns(Num_Col).Hidden = True
        Else
            Columns(Num_Col).Hidden = False
        End If
    Next Num_Col
End Sub

Se nao ta claro, vou explicar mais.

Boa sorte

 

[GuardianZer0]

Fantastic =D it worked =D

now, can you please explain me why it worked?

 

[ExcelGuyDude]

First off, I'm setting the variable "LastDate" equal to a date. That way you can add, subtract, or do anything you want in Date-Like increments.

LastDate = DateAdd("m", 1, Range("D18") - 1) 'What this does is captures the date in cell D18, adds one month to that day, then subtracts one day. That will always be the 20th of the next month.
The next part is the loop... It will look through columns 29 through 35 (which is a little over kill). The purpose of looping is to search each column and check if it's within the correct date range.

Now within the loop, there is an IF statement. It's checking row 18 and the columns 29 through 35. For each of those cells, if the date in the cell is greater than the variable "LastDate", then it will hide the column. Otherwise it will show the column.

Sub Hide_Day()
    Dim LastDate As Date, Num_Col As Integer
    LastDate = DateAdd("m", 1, Range("D18") - 1)    'Setting the date variable to the 20th of the next month (month after the value in cell D18)
    For Num_Col = 29 To 35 'Looping through columns 29 to 35.  This is to capture all the last columns that may be outside the date range.
        If Cells(18, Num_Col) > LastDate Then   'If the cell value in column x (Num_Col number which will be between 29 and 35, or column AC to AI)
                                                'and row 18 is greater than the date set above ("LastDate"), then...
            Columns(Num_Col).Hidden = True      'if the statement is true, hide the column.
        Else
            Columns(Num_Col).Hidden = False     'if the statement is false, unhide the column.
        End If
    Next Num_Col
End Sub

If you are having difficulty seeing what's going on, you can manually walk through each step of the macro by hitting F8 on your keyboard (assuming you're cursor is active in the correct macro.) As you go through, it will highlight the next action that will be preformed in yellow. You can hover your mouse over the variables LastDate and Num_Col to see what they're currently set to, or you can place "Watches" on them to see how they change as you go through the macro.

 

[GuardianZer0]

I see.. it makes much more sense now.
It was great help from you =D i wasn't getting anywhere near that macro yet xP

meanwhile, i found some simpler stuff, like clearing the table and filtering, do you have any other ideas that would be a nice addiction to the table?
If so, i'd be happy to hear them (and with an explanation if possible) as learning excel is always a good thing
If not, then i'll be thanking you for your time and help, it really made a difference for me =D

 

[Simon92]

Hi,


I having same problem for the hidden cell. Could u help me for the code?

Sub Hide_Day()
Dim Num_Col As Long
For Num_Col = 36 To 38
If Month(Cells(8, Num_Col)) <> Cells(1, 1) Then
Columns(Num_Col).Hidden = True
Else
Columns(Num_Col).Hidden = False
End If
Next
End Sub

View image: Excel

 

[GuardianZer0]

Hey

Try this:

If Month(Cells(4, Num_Col)) <> Cells(2, 3) Then