Macro sometimes works and sometimes doesnt.

R

rxharp419

Board Regular
Joined
Mar 12, 2015
Messages
58
Hello, i have the following code i want to use for a button. It will random;y work like sometime when i open the sheet but then i start getting error codes like <style type="text/css"> p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px 'Helvetica Neue'} p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px 'Helvetica Neue'; min-height: 12.0px} </style>
Run-time error '1004':


Unable to set the Hidden property of the Range class

and it debugs on this

<style type="text/css"> p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000 ; background-color: #ffffff } p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; background-color: #ffffff ; min-height: 13.0px} span.s1 {color: #011993 } </style>
Sub UnHideBlankDays()


If Range("B9") = "" Then
Rows("7:23").EntireRow.Hidden = False
End If

But other times it would work just fine.

My code is long and I also have another one just like this with True values on everything assigned to another bottom to hide all the rows.

<style type="text/css"> p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000 ; background-color: #ffffff } p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; background-color: #ffffff ; min-height: 13.0px} p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #011993 ; background-color: #ffffff } span.s1 {color: #011993 } span.s2 {color: #000000 } </style>
Sub UnHideBlankDays()


If Range("B9") = "" Then
Rows("7:23").EntireRow.Hidden = False
End If


If Range("B26") = "" Then
Rows("24:40").EntireRow.Hidden = False
End If


If Range("B43") = "" Then
Rows("41:57").EntireRow.Hidden = False
End If


If Range("B60") = "" Then
Rows("58:74").EntireRow.Hidden = False
End If


If Range("B77") = "" Then
Rows("75:91").EntireRow.Hidden = False
End If


If Range("B94") = "" Then
Rows("92:108").EntireRow.Hidden = False
End If


If Range("B111") = "" Then
Rows("109:125").EntireRow.Hidden = False
End If


If Range("B128") = "" Then
Rows("126:142").EntireRow.Hidden = False
End If


If Range("B145") = "" Then
Rows("143:159").EntireRow.Hidden = False
End If


If Range("B162") = "" Then
Rows("160:176").EntireRow.Hidden = False
End If


If Range("B179") = "" Then
Rows("177:193").EntireRow.Hidden = False
End If


If Range("B196") = "" Then
Rows("194:209").EntireRow.Hidden = False
End If


If Range("B213") = "" Then
Rows("211:227").EntireRow.Hidden = False
End If


If Range("B230") = "" Then
Rows("228:244").EntireRow.Hidden = False
End If


If Range("B247") = "" Then
Rows("245:261").EntireRow.Hidden = False
End If


If Range("B264") = "" Then
Rows("262:278").EntireRow.Hidden = False
End If


If Range("B281") = "" Then
Rows("279:295").EntireRow.Hidden = False
End If


If Range("B298") = "" Then
Rows("296:312").EntireRow.Hidden = False
End If


If Range("B315") = "" Then
Rows("313:329").EntireRow.Hidden = False
End If


If Range("B332") = "" Then
Rows("330:346").EntireRow.Hidden = False
End If


If Range("B349") = "" Then
Rows("347:363").EntireRow.Hidden = False
End If


If Range("B366") = "" Then
Rows("364:380").EntireRow.Hidden = False
End If


If Range("B383") = "" Then
Rows("381:397").EntireRow.Hidden = False
End If


If Range("B400") = "" Then
Rows("398:414").EntireRow.Hidden = False
End If


If Range("B417") = "" Then
Rows("415:431").EntireRow.Hidden = False
End If


If Range("B434") = "" Then
Rows("432:448").EntireRow.Hidden = False
End If


If Range("B451") = "" Then
Rows("449:465").EntireRow.Hidden = False
End If


If Range("B468") = "" Then
Rows("466:482").EntireRow.Hidden = False
End If


If Range("B485") = "" Then
Rows("483:499").EntireRow.Hidden = False
End If


If Range("B502") = "" Then
Rows("500:516").EntireRow.Hidden = False
End If


If Range("B519") = "" Then
Rows("517:533").EntireRow.Hidden = False
End If


If Range("B536") = "" Then
Rows("534:550").EntireRow.Hidden = False
End If


If Range("B553") = "" Then
Rows("551:567").EntireRow.Hidden = False
End If


If Range("B570") = "" Then
Rows("568:584").EntireRow.Hidden = False
End If


If Range("B587") = "" Then
Rows("585:601").EntireRow.Hidden = False
End If


If Range("B604") = "" Then
Rows("602:618").EntireRow.Hidden = False
End If


If Range("B621") = "" Then
Rows("619:635").EntireRow.Hidden = False
End If


If Range("B638") = "" Then
Rows("636:652").EntireRow.Hidden = False
End If


If Range("B655") = "" Then
Rows("653:669").EntireRow.Hidden = False
End If


If Range("B672") = "" Then
Rows("670:686").EntireRow.Hidden = False
End If


If Range("B689") = "" Then
Rows("687:703").EntireRow.Hidden = False
End If


If Range("B706") = "" Then
Rows("704:720").EntireRow.Hidden = False
End If


If Range("B723") = "" Then
Rows("721:737").EntireRow.Hidden = False
End If


If Range("B740") = "" Then
Rows("738:754").EntireRow.Hidden = False
End If


If Range("B757") = "" Then
Rows("755:771").EntireRow.Hidden = False
End If


If Range("B774") = "" Then
Rows("772:788").EntireRow.Hidden = False
End If


If Range("B791") = "" Then
Rows("789:805").EntireRow.Hidden = False
End If


If Range("B808") = "" Then
Rows("806:822").EntireRow.Hidden = False
End If


If Range("B825") = "" Then
Rows("823:839").EntireRow.Hidden = False
End If


If Range("B842") = "" Then
Rows("840:856").EntireRow.Hidden = False
End If


If Range("B859") = "" Then
Rows("857:873").EntireRow.Hidden = False
End If


If Range("B876") = "" Then
Rows("874:890").EntireRow.Hidden = False
End If


If Range("B893") = "" Then
Rows("891:907").EntireRow.Hidden = False
End If


If Range("B910") = "" Then
Rows("908:924").EntireRow.Hidden = False
End If


If Range("B927") = "" Then
Rows("925:941").EntireRow.Hidden = False
End If


If Range("B944") = "" Then
Rows("942:958").EntireRow.Hidden = False
End If


If Range("B961") = "" Then
Rows("959:975").EntireRow.Hidden = False
End If


If Range("B978") = "" Then
Rows("976:992").EntireRow.Hidden = False
End If


If Range("B995") = "" Then
Rows("993:1009").EntireRow.Hidden = False
End If


If Range("B1012") = "" Then
Rows("1010:1026").EntireRow.Hidden = False
End If



End Sub
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
You could shorten that a little:

Code: 
Sub UnHideBlankDays()
  Dim iRow          As Long

  For iRow = 9 To 1012 Step 17
    If IsEmpty(Cells(iRow, "B")) Then Rows(iRow - 2).Resize(17).Hidden = False
  Next iRow
End Sub
 
Upvote 0
Or do both with the same procedure:

Code: 
Sub main()
  SetBlankDays bVisible:=True
  SetBlankDays bVisible:=False
End Sub

Sub SetBlankDays(bVisible As Boolean)
  Dim iRow          As Long

  For iRow = 9 To 1012 Step 17
    If IsEmpty(Cells(iRow, "B")) Then Rows(iRow - 2).Resize(17).Hidden = bVisible
  Next iRow
End Sub
 
Upvote 0
shg said:
Or do both with the same procedure:

Code: 
Sub main()
  SetBlankDays bVisible:=True
  SetBlankDays bVisible:=False
End Sub

Sub SetBlankDays(bVisible As Boolean)
  Dim iRow          As Long

  For iRow = 9 To 1012 Step 17
    If IsEmpty(Cells(iRow, "B")) Then Rows(iRow - 2).Resize(17).Hidden = bVisible
  Next iRow
End Sub
Click to expand...


Well that would certainly be nice!

Okay, i tried to copy and paste this but it doesn't seem to work. Is there something i need to change?

I have 2 separate buttons(Hide and Unhide) and the each have 2 macros within them.

The Hide button has the below macros

<style type="text/css"> p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #011993 ; background-color: #ffffff } p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000 ; background-color: #ffffff } p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; background-color: #ffffff ; min-height: 13.0px} span.s1 {color: #000000 } span.s2 {color: #011993 } </style>Option Explicit
Public Sub Hide()
Dim r As Range, c As Range
Set r = Range("C9:C20,C26:C37,C43:C54,C60:C71,C77:C88,C94:C105,C111:C122,C128:C139,C145:C156,C162:C173,C179:C190,C196:C207,C213:C224,C230:C241,C247:C258,C264:C275,C281:C292,C298:C309,C315:C326,C332:C343,C349:C360,C366:C377,C383:C394,C400:C411,C417:C428,C434:C445,C451:C462,C468:C479,C485:C496,C502:C513,C519:C530,C536:C547,C553:C564,C570:C581,C587:C598,C604:C615,C621:C632,C638:C649,C655:C666,C672:C683,C689:C700,C706:C717,C723:C734,C740:C751,C757:C768,C774:C785,C791:C802,C808:C819,C825:C836,C842:C853,C859:C870,C876:C887,C893:C904,C910:C921,C927:C938,C944:C955,C961:C972,C978:C989,C995:C1006,C1012:C1023,E1034,E1035,E1036,F1038,F1039,F1040,F1041,F1042,F1043,F1044,E1046,E1047,E1048,E1049,E1050,E1052,E1053,E1054,E1055,E1056,E1058,E1059,E1060,E1061,E1062,E1064,F1037,F1045,F1051")
Application.ScreenUpdating = True
For Each c In r
If Len(c.Text) = 0 Then
c.EntireRow.Hidden = True
Else
c.EntireRow.Hidden = False
End If
Next c
Application.ScreenUpdating = False


[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=011993]#011993[/URL] ]End[/COLOR] [COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=011993]#011993[/URL] ]Sub[/COLOR]

<style type="text/css"> p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000 ; background-color: #ffffff } p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; background-color: #ffffff ; min-height: 13.0px} p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #011993 ; background-color: #ffffff } span.s1 {color: #011993 } span.s2 {color: #000000 } </style>
Public Sub HideBlankDays()


If Range("B9") = "" Then
Rows("7:23").EntireRow.Hidden = True
End If


If Range("B26") = "" Then
Rows("24:40").EntireRow.Hidden = True
End If


If Range("B43") = "" Then
Rows("41:57").EntireRow.Hidden = True
End If


If Range("B60") = "" Then
Rows("58:74").EntireRow.Hidden = True
End If


If Range("B77") = "" Then
Rows("75:91").EntireRow.Hidden = True
End If


If Range("B94") = "" Then
Rows("92:108").EntireRow.Hidden = True
End If


If Range("B111") = "" Then
Rows("109:125").EntireRow.Hidden = True
End If


If Range("B128") = "" Then
Rows("126:142").EntireRow.Hidden = True
End If


If Range("B145") = "" Then
Rows("143:159").EntireRow.Hidden = True
End If


If Range("B162") = "" Then
Rows("160:176").EntireRow.Hidden = True
End If


If Range("B179") = "" Then
Rows("177:193").EntireRow.Hidden = True
End If


If Range("B196") = "" Then
Rows("194:210").EntireRow.Hidden = True
End If


If Range("B213") = "" Then
Rows("211:227").EntireRow.Hidden = True
End If


If Range("B230") = "" Then
Rows("228:244").EntireRow.Hidden = True
End If


If Range("B247") = "" Then
Rows("245:261").EntireRow.Hidden = True
End If


If Range("B264") = "" Then
Rows("262:278").EntireRow.Hidden = True
End If


If Range("B281") = "" Then
Rows("279:295").EntireRow.Hidden = True
End If


If Range("B298") = "" Then
Rows("296:312").EntireRow.Hidden = True
End If


If Range("B315") = "" Then
Rows("313:329").EntireRow.Hidden = True
End If


If Range("B332") = "" Then
Rows("330:346").EntireRow.Hidden = True
End If


If Range("B349") = "" Then
Rows("347:363").EntireRow.Hidden = True
End If


If Range("B366") = "" Then
Rows("364:380").EntireRow.Hidden = True
End If


If Range("B383") = "" Then
Rows("381:397").EntireRow.Hidden = True
End If


If Range("B400") = "" Then
Rows("398:414").EntireRow.Hidden = True
End If


If Range("B417") = "" Then
Rows("415:431").EntireRow.Hidden = True
End If


If Range("B434") = "" Then
Rows("432:448").EntireRow.Hidden = True
End If


If Range("B451") = "" Then
Rows("449:465").EntireRow.Hidden = True
End If


If Range("B468") = "" Then
Rows("466:482").EntireRow.Hidden = True
End If


If Range("B485") = "" Then
Rows("483:499").EntireRow.Hidden = True
End If


If Range("B502") = "" Then
Rows("500:516").EntireRow.Hidden = True
End If


If Range("B519") = "" Then
Rows("517:533").EntireRow.Hidden = True
End If


If Range("B536") = "" Then
Rows("534:550").EntireRow.Hidden = True
End If


If Range("B553") = "" Then
Rows("551:567").EntireRow.Hidden = True
End If


If Range("B570") = "" Then
Rows("568:584").EntireRow.Hidden = True
End If


If Range("B587") = "" Then
Rows("585:601").EntireRow.Hidden = True
End If


If Range("B604") = "" Then
Rows("602:618").EntireRow.Hidden = True
End If


If Range("B621") = "" Then
Rows("619:635").EntireRow.Hidden = True
End If


If Range("B638") = "" Then
Rows("636:652").EntireRow.Hidden = True
End If


If Range("B655") = "" Then
Rows("653:669").EntireRow.Hidden = True
End If


If Range("B672") = "" Then
Rows("670:686").EntireRow.Hidden = True
End If


If Range("B689") = "" Then
Rows("687:703").EntireRow.Hidden = True
End If


If Range("B706") = "" Then
Rows("704:720").EntireRow.Hidden = True
End If


If Range("B723") = "" Then
Rows("721:737").EntireRow.Hidden = True
End If


If Range("B740") = "" Then
Rows("738:754").EntireRow.Hidden = True
End If


If Range("B757") = "" Then
Rows("755:771").EntireRow.Hidden = True
End If


If Range("B774") = "" Then
Rows("772:788").EntireRow.Hidden = True
End If


If Range("B791") = "" Then
Rows("789:805").EntireRow.Hidden = True
End If


If Range("B808") = "" Then
Rows("806:822").EntireRow.Hidden = True
End If


If Range("B825") = "" Then
Rows("823:839").EntireRow.Hidden = True
End If


If Range("B842") = "" Then
Rows("840:856").EntireRow.Hidden = True
End If


If Range("B859") = "" Then
Rows("857:873").EntireRow.Hidden = True
End If


If Range("B876") = "" Then
Rows("874:890").EntireRow.Hidden = True
End If


If Range("B893") = "" Then
Rows("891:907").EntireRow.Hidden = True
End If


If Range("B910") = "" Then
Rows("908:924").EntireRow.Hidden = True
End If


If Range("B927") = "" Then
Rows("925:941").EntireRow.Hidden = True
End If


If Range("B944") = "" Then
Rows("942:958").EntireRow.Hidden = True
End If


If Range("B961") = "" Then
Rows("959:975").EntireRow.Hidden = True
End If


If Range("B978") = "" Then
Rows("976:992").EntireRow.Hidden = True
End If


If Range("B995") = "" Then
Rows("993:1009").EntireRow.Hidden = True
End If


If Range("B1012") = "" Then
Rows("1010:1026").EntireRow.Hidden = True
End If


End Sub

I created a Master macro for those 2 above that is actually linked to the button.

<style type="text/css"> p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000 ; background-color: #ffffff } p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; background-color: #ffffff ; min-height: 13.0px} p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #011993 ; background-color: #ffffff } span.s1 {color: #011993 } span.s2 {color: #000000 } </style>
Sub MasterHideBlanks()


Call Hide


Call HideBlankDays


End Sub






Than, I have the same thing for the Unhide button. In this order though:


<style type="text/css"> p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000 ; background-color: #ffffff } p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; background-color: #ffffff ; min-height: 13.0px} p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #011993 ; background-color: #ffffff } span.s1 {color: #011993 } span.s2 {color: #000000 } </style>
Sub UnHideBlankDays()


If Range("B9") = "" Then
Rows("7:23").EntireRow.Hidden = False
End If


If Range("B26") = "" Then
Rows("24:40").EntireRow.Hidden = False
End If


If Range("B43") = "" Then
Rows("41:57").EntireRow.Hidden = False
End If


If Range("B60") = "" Then
Rows("58:74").EntireRow.Hidden = False
End If


If Range("B77") = "" Then
Rows("75:91").EntireRow.Hidden = False
End If


If Range("B94") = "" Then
Rows("92:108").EntireRow.Hidden = False
End If


If Range("B111") = "" Then
Rows("109:125").EntireRow.Hidden = False
End If


If Range("B128") = "" Then
Rows("126:142").EntireRow.Hidden = False
End If


If Range("B145") = "" Then
Rows("143:159").EntireRow.Hidden = False
End If


If Range("B162") = "" Then
Rows("160:176").EntireRow.Hidden = False
End If


If Range("B179") = "" Then
Rows("177:193").EntireRow.Hidden = False
End If


If Range("B196") = "" Then
Rows("194:210").EntireRow.Hidden = False
End If


If Range("B213") = "" Then
Rows("211:227").EntireRow.Hidden = False
End If


If Range("B230") = "" Then
Rows("228:244").EntireRow.Hidden = False
End If


If Range("B247") = "" Then
Rows("245:261").EntireRow.Hidden = False
End If


If Range("B264") = "" Then
Rows("262:278").EntireRow.Hidden = False
End If


If Range("B281") = "" Then
Rows("279:295").EntireRow.Hidden = False
End If


If Range("B298") = "" Then
Rows("296:312").EntireRow.Hidden = False
End If


If Range("B315") = "" Then
Rows("313:329").EntireRow.Hidden = False
End If


If Range("B332") = "" Then
Rows("330:346").EntireRow.Hidden = False
End If


If Range("B349") = "" Then
Rows("347:363").EntireRow.Hidden = False
End If


If Range("B366") = "" Then
Rows("364:380").EntireRow.Hidden = False
End If


If Range("B383") = "" Then
Rows("381:397").EntireRow.Hidden = False
End If


If Range("B400") = "" Then
Rows("398:414").EntireRow.Hidden = False
End If


If Range("B417") = "" Then
Rows("415:431").EntireRow.Hidden = False
End If


If Range("B434") = "" Then
Rows("432:448").EntireRow.Hidden = False
End If


If Range("B451") = "" Then
Rows("449:465").EntireRow.Hidden = False
End If


If Range("B468") = "" Then
Rows("466:482").EntireRow.Hidden = False
End If


If Range("B485") = "" Then
Rows("483:499").EntireRow.Hidden = False
End If


If Range("B502") = "" Then
Rows("500:516").EntireRow.Hidden = False
End If


If Range("B519") = "" Then
Rows("517:533").EntireRow.Hidden = False
End If


If Range("B536") = "" Then
Rows("534:550").EntireRow.Hidden = False
End If


If Range("B553") = "" Then
Rows("551:567").EntireRow.Hidden = False
End If


If Range("B570") = "" Then
Rows("568:584").EntireRow.Hidden = False
End If


If Range("B587") = "" Then
Rows("585:601").EntireRow.Hidden = False
End If


If Range("B604") = "" Then
Rows("602:618").EntireRow.Hidden = False
End If


If Range("B621") = "" Then
Rows("619:635").EntireRow.Hidden = False
End If


If Range("B638") = "" Then
Rows("636:652").EntireRow.Hidden = False
End If


If Range("B655") = "" Then
Rows("653:669").EntireRow.Hidden = False
End If


If Range("B672") = "" Then
Rows("670:686").EntireRow.Hidden = False
End If


If Range("B689") = "" Then
Rows("687:703").EntireRow.Hidden = False
End If


If Range("B706") = "" Then
Rows("704:720").EntireRow.Hidden = False
End If


If Range("B723") = "" Then
Rows("721:737").EntireRow.Hidden = False
End If


If Range("B740") = "" Then
Rows("738:754").EntireRow.Hidden = False
End If


If Range("B757") = "" Then
Rows("755:771").EntireRow.Hidden = False
End If


If Range("B774") = "" Then
Rows("772:788").EntireRow.Hidden = False
End If


If Range("B791") = "" Then
Rows("789:805").EntireRow.Hidden = False
End If


If Range("B808") = "" Then
Rows("806:822").EntireRow.Hidden = False
End If


If Range("B825") = "" Then
Rows("823:839").EntireRow.Hidden = False
End If


If Range("B842") = "" Then
Rows("840:856").EntireRow.Hidden = False
End If


If Range("B859") = "" Then
Rows("857:873").EntireRow.Hidden = False
End If


If Range("B876") = "" Then
Rows("874:890").EntireRow.Hidden = False
End If


If Range("B893") = "" Then
Rows("891:907").EntireRow.Hidden = False
End If


If Range("B910") = "" Then
Rows("908:924").EntireRow.Hidden = False
End If


If Range("B927") = "" Then
Rows("925:941").EntireRow.Hidden = False
End If


If Range("B944") = "" Then
Rows("942:958").EntireRow.Hidden = False
End If


If Range("B961") = "" Then
Rows("959:975").EntireRow.Hidden = False
End If


If Range("B978") = "" Then
Rows("976:992").EntireRow.Hidden = False
End If


If Range("B995") = "" Then
Rows("993:1009").EntireRow.Hidden = False
End If


If Range("B1012") = "" Then
Rows("1010:1026").EntireRow.Hidden = False
End If


End Sub

<style type="text/css"> p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #011993 ; background-color: #ffffff } p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000 ; background-color: #ffffff } p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; background-color: #ffffff ; min-height: 13.0px} span.s1 {color: #000000 } span.s2 {color: #011993 } </style>
Option Explicit
Public Sub UnHide()
Dim r As Range, c As Range
Set r = Range("C9:C20,C26:C37,C43:C54,C60:C71,C77:C88,C94:C105,C111:C122,C128:C139,C145:C156,C162:C173,C179:C190,C196:C207,C213:C224,C230:C241,C247:C258,C264:C275,C281:C292,C298:C309,C315:C326,C332:C343,C349:C360,C366:C377,C383:C394,C400:C411,C417:C428,C434:C445,C451:C462,C468:C479,C485:C496,C502:C513,C519:C530,C536:C547,C553:C564,C570:C581,C587:C598,C604:C615,C621:C632,C638:C649,C655:C666,C672:C683,C689:C700,C706:C717,C723:C734,C740:C751,C757:C768,C774:C785,C791:C802,C808:C819,C825:C836,C842:C853,C859:C870,C876:C887,C893:C904,C910:C921,C927:C938,C944:C955,C961:C972,C978:C989,C995:C1006,C1012:C1023,E1034,E1035,E1036,F1038,F1039,F1040,F1041,F1042,F1043,F1044,E1046,E1047,E1048,E1049,E1050,E1052,E1053,E1054,E1055,E1056,E1058,E1059,E1060,E1061,E1062,E1064,F1037,F1045,F1051")
Application.ScreenUpdating = True
For Each c In r
If Len(c.Text) = 0 Then
c.EntireRow.Hidden = False
Else
c.EntireRow.Hidden = False
End If
Next c
Application.ScreenUpdating = False
End Sub


[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]I created a Master macro for those 2 above that is linked to the unhide button.

[/COLOR]<style type="text/css"> p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000 ; background-color: #ffffff } p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; background-color: #ffffff ; min-height: 13.0px} p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #011993 ; background-color: #ffffff } span.s1 {color: #011993 } span.s2 {color: #000000 } </style>
Sub MasterUnhideBlanks()


Call UnHideBlankDays
Call UnHide


End Sub



So now you can see exactly what i am doing. It appears you had a much easier macro in your previous quote it just did not work for me when i put it in.

Like i said, the one above is now working but i would love to have an easier method.

Thank you in advance!





























<style type="text/css"> p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #011993 ; background-color: #ffffff } p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000 ; background-color: #ffffff } p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; background-color: #ffffff ; min-height: 13.0px} span.s1 {color: #000000 } span.s2 {color: #011993 } </style>
 
Upvote 0
The first sub doesn't run at all -- the range string is too long.
 
Upvote 0
The argument in the procedure I posted is mis-named; it should be

Code: 
Sub main()
  SetBlankDays bHidden:=True
  ' or
  SetBlankDays bHidden:=False
End Sub

Sub SetBlankDays([COLOR="#FF0000"]bHidden [/COLOR]As Boolean)
  Dim iRow          As Long

  For iRow = 9 To 1012 Step 17
    If IsEmpty(Cells(iRow, "B")) Then Rows(iRow - 2).Resize(17).Hidden = bHidden
  Next iRow
End Sub
 
Upvote 0
shg said:
The argument in the procedure I posted is mis-named; it should be

Code: 
Sub main()
  SetBlankDays bHidden:=True
  ' or
  SetBlankDays bHidden:=False
End Sub

Sub SetBlankDays([COLOR=#FF0000]bHidden [/COLOR]As Boolean)
  Dim iRow          As Long

  For iRow = 9 To 1012 Step 17
    If IsEmpty(Cells(iRow, "B")) Then Rows(iRow - 2).Resize(17).Hidden = bHidden
  Next iRow
End Sub
Click to expand...

Hello, I cannot get this to work. I am doing something wrong. Can i get rid of the old code i was trying to use and use this one?
 
Upvote 0
Hello, I cannot get this to work. I am doing something wrong.
Click to expand...

What happens when you step through the code?

Can i get rid of the old code i was trying to use and use this one?
Click to expand...

All the code in post#1, yes.
 
Upvote 0
OKay since that code i actually made some changes i did not realize. Apologies.

So in my first post, I had this sequence

Code: 
[COLOR=#333333]If Range("B9") = "" Then[/COLOR]
[COLOR=#333333]Rows("7:23").EntireRow.Hidden = False[/COLOR]
[COLOR=#333333]End If[/COLOR]


[COLOR=#333333]If Range("B26") = "" Then[/COLOR]
[COLOR=#333333]Rows("24:40").EntireRow.Hidden = False[/COLOR]
[COLOR=#333333]End If[/COLOR]


[COLOR=#333333]If Range("B43") = "" Then[/COLOR]
[COLOR=#333333]Rows("41:57").EntireRow.Hidden = False[/COLOR]
[COLOR=#333333]End If[/COLOR]

Everything shifted +2 because I had to add 2 rows on the top. So now It would look like this:

Code: 
[COLOR=#333333]If Range("B11") = "" Then[/COLOR]
[COLOR=#333333]Rows("9:25").EntireRow.Hidden = False[/COLOR]
[COLOR=#333333]End If[/COLOR]


[COLOR=#333333]If Range("B28") = "" Then[/COLOR]
[COLOR=#333333]Rows("26:42").EntireRow.Hidden = False[/COLOR]
[COLOR=#333333]End If[/COLOR]


[COLOR=#333333]If Range("B45") = "" Then[/COLOR]
[COLOR=#333333]Rows("43:559").EntireRow.Hidden = False[/COLOR]
[COLOR=#333333]End If[/COLOR]

So i tried your code with some adjustments in it to accomadate my changes but still wont work. When i try to run it, it doesnt do anything, no sign of anything. Not sure what i am doing wrong.

This is what i have now

<style type="text/css"> p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000 ; background-color: #ffffff } p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #011993 ; background-color: #ffffff } span.s1 {color: #011993 } span.s2 {color: #000000 } </style>
Code: 
Sub main()
SetBlankDays bHidden:=True
SetBlankDays bHidden:=False
End Sub
Sub SetBlankDays(bHidden As Boolean)
  Dim iRow          As Long
  For iRow = 11 To 1014 Step 17
    If IsEmpty(Cells(iRow, "B")) Then Rows(iRow - 2).Resize(17).Hidden = bHidden
  Next iRow
End Sub

Also, how i can this code be used when the sheet is locked or protexted with a password? this would be a must.
 
Upvote 0
This ...

Code: 
Sub main()
  SetBlankDays bHidden:=True
  SetBlankDays bHidden:=False
End Sub

... hides the rows and then immediately unhides them. Use one line or the other, depending on what your trying to do.

Time to learn to debug: http://www.cpearson.com/excel/DebuggingVBA.aspx
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
1K
AndyEd
A
E
  • Question Question
Adding a custom message.
Replies
1
Views
671
Micron
M
E
  • Question Question
Create mails with table - standard header specific row
Replies
1
Views
1K
esben
E
E
  • Question Question
Query in excel to Outlook mail take rows with name until next name
Replies
0
Views
519
esben
E
A
  • Solved
Calendar issue
Replies
1
Views
2K
AndyEd
A

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

Share this page

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