How to hide a cell if the date is equal to the 1st of the month

Hmerman

Board Regular
Joined
Oct 2, 2016
Messages
102
Hello all,

I am trying to tell a cell to hide if the date in the cell is equal to the first of the month.

I am struggling with the code to check if the cell is equal to the first of the month.

Here is my
Code:
If DateValue(Date("yyyy/mm/01")) = DateValue(Range("A32")) then
    Selection.EntireRow.Hidden = True

End if
Can one of you fine forum please help with the date value calculation?

Friendly Regards
Herman
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,643
Try this:
Code:
If Day(DateValue(Range("A32"))) = 1 Then Selection.EntireRow.Hidden = True
 

Hmerman

Board Regular
Joined
Oct 2, 2016
Messages
102
Thank you very much, the date calculation works very well. I really like your style, it is understandable and clear as well as being effective.

I had to tweak my Hide command by selecting the Rows("32:32").

Here is my
Code:
If Day(DateValue(Range("A32"))) = 1 Then
    Rows("32:32").EntireRow.Hidden = True
End If

Friendly Regards
Herman
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,052
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
@Hmerman, just a suggestion but you can just use the range again rather than use Rows i.e.

Code:
Sub hideit()

If Day(DateValue(Range("A32"))) = 1 Then Range("A32").EntireRow.Hidden = True

End Sub

or probably a better way of doing it...

Code:
Sub hideit2()
Dim rng As Range

Set rng = Range("A32")

If Day(DateValue(rng)) = 1 Then rng.EntireRow.Hidden = True

End Sub

but if you are using Rows then you don't need the .EntireRow as Rows already refers to the entire row i.e.

Code:
Sub hideit3()
If Day(DateValue(Range("A32"))) = 1 Then Rows("32:32").Hidden = True
End Sub

and as you see you don't need the End If's in any of the codes as you are only doing a single test and so it can all be on one line (as Tetra201 also did if you look at the code in post #2).
 

Hmerman

Board Regular
Joined
Oct 2, 2016
Messages
102
@MARK858

Sorry for not replying earlier. Was a bit swamped with work worries.

I have taken your advice into account and it made for a much improved code.

Thank you once again!

Friendly Regards
 

Watch MrExcel Video

Forum statistics

Threads
1,129,800
Messages
5,638,430
Members
417,025
Latest member
MusterDuster

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top