Macro to amend row height

[kerrence]

Hi there,
I am trying to hide rows in a spreadsheet with no success and am hoping a macro will help. I have the first column with the date but do not want any lines which are the following month to show. In the image all rows are needed however for a shorter month they are not. I want to add a macro to hide B34 if the month in B4 does not match. I will also need to run this for B33 and B32.

Can anyone give a total novice a hand? Many thanks!

 

[KOKOSEK]

Hmm.. maybe you can solve this without VBA.
Put this into B5:

=IFERROR(IF((B4+1)<=EOMONTH(B4,0),B4+1,""),"")

and drag it down to B34. It should showing only dates which are in month of date from B4.

 

[kerrence]

Hmm.. maybe you can solve this without VBA.
Put this into B5:

=IFERROR(IF((B4+1)<=EOMONTH(B4,0),B4+1,""),"")

and drag it down to B34. It should showing only dates which are in month of date from B4.

Hi Kokosek,
The end user wants no rows showing at all annoyingly! I had already used conditional formatting to white out the date if not required but there is formatting in later columns depending if it is a weekend or not so this still shows.
thanks!

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[KOKOSEK]

OK.
So, into Worksheet code put:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
If Not Intersect([B4], Target) Is Nothing Then
    Rows("4:34").Hidden = False
    For Each cell In [B32:B34]
        If Month(cell.Value) <> Month([B4].Value) Then Rows(cell.Row).Hidden = True
    Next cell
End If
End Sub

 

[kerrence]

OK.
So, into Worksheet code put:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
If Not Intersect([B4], Target) Is Nothing Then
    Rows("4:34").Hidden = False
    For Each cell In [B32:B34]
        If Month(cell.Value) <> Month([B4].Value) Then Rows(cell.Row).Hidden = True
    Next cell
End If
End Sub

Thank you for the code but I am not getting it to run. I am uploading my worksheet so any pointers about where I am going wrong would be amazing!

 

[KOKOSEK]

Did you put this code into sheet not into module?
Like this:

I suppose that cells B5:B34 are calculated automatically based on starting B4.
If not change this:

If Not Intersect([B4], Target) Is Nothing Then

into this:

If Not Intersect([B4:B34], Target) Is Nothing Then

maybe this helps.

 

[kerrence]

Did you put this code into sheet not into module?
Like this:

View attachment 50385
I suppose that cells B5:B34 are calculated automatically based on starting B4.
If not change this:

If Not Intersect([B4], Target) Is Nothing Then

into this:

If Not Intersect([B4:B34], Target) Is Nothing Then

maybe this helps.

You are my hero! My manager tasked me this knowing I am not versed in VBA and I have been struggling with it so much. You are an absolute hero!

 

[KOKOSEK]

No problem. Happy to help.