Conditional row Hiding

Newbie212

New Member
Joined
Oct 15, 2018
Messages
16
Office Version
  1. 2010
Platform
  1. Windows
Hello Excel Masters! I have a monthly schedule table - that is generated month by month from drop down menu by choosing the month. There is 5 columns - A is a merged 60 row cell that is just a date in dd.mm. /dddd/ format, and B,C,D and E are 60 free rows for the actual events.

What im trying to do is when i generate the calendar - to have a macro that Hides 50 rows if the Cell in A is Saturday or Sunday.

So far i have this:

HTML:
Sub HideRows()
    BeginRow = 9
    EndRow = 57
    ChkCol = 6 

          For RowCnt = BeginRow To EndRow  
      If Cells(RowCnt, ChkCol).Value < 51 Then
            Cells(RowCnt, ChkCol).EntireRow.Hidden = True
        End If

           Next RowCnt 
       End Sub

Column 6 is a help column with 1-60 next to each day.

HTML:
Sub DayCheck()
If G6 > 5 Then
HideRows
Else
MsgBox "NOT OK"
End If
End Sub

G6 =WEEKDAY($A$9;2)

Seams simple enough... but it doesn't work properly and i don't know what is the problem. I did some testing the macro is getting the right result(G6>5) ... but it jumps directly to the else and does nothing. Help please!
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Column 6 is F, not G.

Also, in your HideRows macro you have "< 51". Should this be "< 5" ?
 
Upvote 0
0YeAgnf
0YeAgnf
0YeAgnf
https://imgur.com/a/0YeAgnf <-- Example of how a single day of the table generally looks like. Sorry if i didnt explain correctly.

G6 is a hidden cell i use for reference it returns 1 to 7 values (Monday=1 / Sunday=7). I use "G6=WEEKDAY(A9;2)>5" to check if its weekend.

And column F(6) is a help column that has just numbered rows next to each day. "<51" seamed the easiest way to leave the last 10 rows of the day visible.

Thanks for the super fast reply ;).
 
Upvote 0
The formula in your last post (=WEEKDAY(A9;2)>5) does not return the weekday numbers. It returns either True(1) or False(0).
Either you need to amend your DayCheck macro or change the G6 formula to :
=WEEKDAY(A9;2)

The HideRows macro could be simplified to :
Code:
Sub HideRows()
Dim beginrow%: beginrow = 9
Rows(beginrow & ":50").Hidden = True
End Sub
Putting sequential numbers in column F appears to be unnecessary
 
Last edited:
Upvote 0
I like the HideRows macro, looks a lot more tidier than mine. G6 is =WEEKDAY(A9;2) in the previous post i say "G6=WEEKDAY(A9;2)>5" to describe my general idea.

HTML:
Sub DayCheck()
If G6 > 5 Then
HideRows
Else
'do nothing'
End If End Sub

I really need to look into this macro. Because this is where my problems seems to be - it jumps directly to the else. And if reverse the logic (G6<5) it works on everything. I tried having a reference cell that returns TRUE(1)/FALSE(0) instead of weekday value, same result.

Thanks for the help!
:biggrin:
 
Upvote 0
Is A9 the first cell in the merged cells?
If not, you need to use the first merged cell ref in your formula.

Also, try this (instead of two macros and using columns F & G) :
Code:
Sub Hide_UnHide_Rows()
If WorksheetFunction.Weekday([A1], 2) > 5 Then
    Rows("9:50").Hidden = True
Else
    Rows("9:50").Hidden = False
End If
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

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
Back
Top