Code hiding or unhiding multiple rows based on cell value

L

lechev65

Board Regular
Joined
Mar 7, 2010
Messages
147
Hi Guys,

I have the below table in Sheet1.
</SPAN>
In B1:B300 I have formula: =IF(Sheet2!B1="","",Sheet2!B1). </SPAN>[TABLE="width: 899"]
<TBODY>[TR]
[TD]
[/TD]
[TD]A</SPAN></SPAN>
[/TD]
[TD]B</SPAN></SPAN>
[/TD]
[TD]C</SPAN></SPAN>
[/TD]
[TD]D</SPAN></SPAN>
[/TD]
[TD]E</SPAN></SPAN>
[/TD]
[TD]F</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]1</SPAN></SPAN>
[/TD]
[TD]1</SPAN></SPAN>
[/TD]
[TD]1717 Certificate of Class</SPAN></SPAN>
[/TD]
[TD]Yes</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]2</SPAN></SPAN>
[/TD]
[TD]2</SPAN></SPAN>
[/TD]
[TD]Anti Fouling Convention</SPAN></SPAN>
[/TD]
[TD]Yes</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]3</SPAN></SPAN>
[/TD]
[TD]3</SPAN></SPAN>
[/TD]
[TD]Performance Standards for Protection Coatings (PSPC)</SPAN></SPAN>
[/TD]
[TD]Yes</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]4</SPAN></SPAN>
[/TD]
[TD]4</SPAN></SPAN>
[/TD]
[TD]ILO No.133 Crew Accommodation</SPAN></SPAN>
[/TD]
[TD]Yes</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]5</SPAN></SPAN>
[/TD]
[TD]5</SPAN></SPAN>
[/TD]
[TD]ILO No. 92 Crew Accommodation</SPAN></SPAN>
[/TD]
[TD]Yes</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]6</SPAN></SPAN>
[/TD]
[TD]6</SPAN></SPAN>
[/TD]
[TD]Flag Administration Regulations</SPAN></SPAN>
[/TD]
[TD]Yes</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]7</SPAN></SPAN>
[/TD]
[TD]7</SPAN></SPAN>
[/TD]
[TD]MLC2006</SPAN></SPAN>
[/TD]
[TD]Yes</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]8</SPAN></SPAN>
[/TD]
[TD]8</SPAN></SPAN>
[/TD]
[TD]OTHERS1</SPAN></SPAN>
[/TD]
[TD]Yes</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]9</SPAN></SPAN>
[/TD]
[TD]9</SPAN></SPAN>
[/TD]
[TD]Form 1424 Certificate of Fitness for the Carriage of Liquefied Gases in Bulk</SPAN></SPAN>
[/TD]
[TD]Yes</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]10</SPAN></SPAN>
[/TD]
[TD]10</SPAN></SPAN>
[/TD]
[TD]Form 1631 International Certificate of Fitness for the Carriage of Liquefied Gases in bulk</SPAN></SPAN>
[/TD]
[TD]Yes</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]11</SPAN></SPAN>
[/TD]
[TD]11</SPAN></SPAN>
[/TD]
[TD]Form 1632 Certificate of Fitness for the Carriage of Liquefied Gases in Bulk</SPAN></SPAN>
[/TD]
[TD]Yes</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]12</SPAN></SPAN>
[/TD]
[TD]12</SPAN></SPAN>
[/TD]
[TD]Form 2220 International Certificate of Fitness for the Carriage of Liquefied Gases in Bulk</SPAN></SPAN>
[/TD]
[TD]Yes</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]13</SPAN></SPAN>
[/TD]
[TD]13</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]Yes</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]14</SPAN></SPAN>
[/TD]
[TD]14</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]Yes</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]15</SPAN></SPAN>
[/TD]
[TD]15</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]No</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]16</SPAN></SPAN>
[/TD]
[TD]16</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]Yes</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]17</SPAN></SPAN>
[/TD]
[TD]17</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]Yes</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]18</SPAN></SPAN>
[/TD]
[TD]18</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]No</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]19</SPAN></SPAN>
[/TD]
[TD]19</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]No</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]20</SPAN></SPAN>
[/TD]
[TD]20</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]Yes</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]21</SPAN></SPAN>
[/TD]
[TD]21</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]Yes</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]22</SPAN></SPAN>
[/TD]
[TD]22</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]Yes</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD].</SPAN></SPAN>
[/TD]
[TD].</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD].</SPAN></SPAN>
[/TD]
[TD].</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD].</SPAN></SPAN>
[/TD]
[TD].</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]300</SPAN></SPAN>
[/TD]
[TD]300</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]No</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

I would like where the formula returns "" in column B cells, a macro to hide automatically the respective rows (13:300 in the example),</SPAN>[TABLE="width: 899"]
<TBODY>[TR]
[TD]
[/TD]
[TD]A</SPAN></SPAN>
[/TD]
[TD]B</SPAN></SPAN>
[/TD]
[TD]C</SPAN></SPAN>
[/TD]
[TD]D</SPAN></SPAN>
[/TD]
[TD]E</SPAN></SPAN>
[/TD]
[TD]F</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]1</SPAN></SPAN>
[/TD]
[TD]1</SPAN></SPAN>
[/TD]
[TD]1717 Certificate of Class</SPAN></SPAN>
[/TD]
[TD]Yes</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]2</SPAN></SPAN>
[/TD]
[TD]2</SPAN></SPAN>
[/TD]
[TD]Anti Fouling Convention</SPAN></SPAN>
[/TD]
[TD]Yes</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]3</SPAN></SPAN>
[/TD]
[TD]3</SPAN></SPAN>
[/TD]
[TD]Performance Standards for Protection Coatings (PSPC)</SPAN></SPAN>
[/TD]
[TD]Yes</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]4</SPAN></SPAN>
[/TD]
[TD]4</SPAN></SPAN>
[/TD]
[TD]ILO No.133 Crew Accommodation</SPAN></SPAN>
[/TD]
[TD]Yes</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]5</SPAN></SPAN>
[/TD]
[TD]5</SPAN></SPAN>
[/TD]
[TD]ILO No. 92 Crew Accommodation</SPAN></SPAN>
[/TD]
[TD]Yes</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]6</SPAN></SPAN>
[/TD]
[TD]6</SPAN></SPAN>
[/TD]
[TD]Flag Administration Regulations</SPAN></SPAN>
[/TD]
[TD]Yes</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]7</SPAN></SPAN>
[/TD]
[TD]7</SPAN></SPAN>
[/TD]
[TD]MLC2006</SPAN></SPAN>
[/TD]
[TD]Yes</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]8</SPAN></SPAN>
[/TD]
[TD]8</SPAN></SPAN>
[/TD]
[TD]OTHERS1</SPAN></SPAN>
[/TD]
[TD]Yes</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]9</SPAN></SPAN>
[/TD]
[TD]9</SPAN></SPAN>
[/TD]
[TD]Form 1424 Certificate of Fitness for the Carriage of Liquefied Gases in Bulk</SPAN></SPAN>
[/TD]
[TD]Yes</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]10</SPAN></SPAN>
[/TD]
[TD]10</SPAN></SPAN>
[/TD]
[TD]Form 1631 International Certificate of Fitness for the Carriage of Liquefied Gases in bulk</SPAN></SPAN>
[/TD]
[TD]Yes</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]11</SPAN></SPAN>
[/TD]
[TD]11</SPAN></SPAN>
[/TD]
[TD]Form 1632 Certificate of Fitness for the Carriage of Liquefied Gases in Bulk</SPAN></SPAN>
[/TD]
[TD]Yes</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]12</SPAN></SPAN>
[/TD]
[TD]12</SPAN></SPAN>
[/TD]
[TD]Form 2220 International Certificate of Fitness for the Carriage of Liquefied Gases in Bulk</SPAN></SPAN>
[/TD]
[TD]Yes</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
</TBODY>[/TABLE]

and to unhide automatically only the rows where the formula result is other than "" in the respective B cell as shown here below.</SPAN>[TABLE="width: 899"]
<TBODY>[TR]
[TD]
[/TD]
[TD]A</SPAN></SPAN>
[/TD]
[TD]B</SPAN></SPAN>
[/TD]
[TD]C</SPAN></SPAN>
[/TD]
[TD]D</SPAN></SPAN>
[/TD]
[TD]E</SPAN></SPAN>
[/TD]
[TD]F</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]1</SPAN></SPAN>
[/TD]
[TD]1</SPAN></SPAN>
[/TD]
[TD]1717 Certificate of Class</SPAN></SPAN>
[/TD]
[TD]Yes</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]2</SPAN></SPAN>
[/TD]
[TD]2</SPAN></SPAN>
[/TD]
[TD]Anti Fouling Convention</SPAN></SPAN>
[/TD]
[TD]Yes</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]3</SPAN></SPAN>
[/TD]
[TD]3</SPAN></SPAN>
[/TD]
[TD]Performance Standards for Protection Coatings (PSPC)</SPAN></SPAN>
[/TD]
[TD]Yes</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]4</SPAN></SPAN>
[/TD]
[TD]4</SPAN></SPAN>
[/TD]
[TD]ILO No.133 Crew Accommodation</SPAN></SPAN>
[/TD]
[TD]Yes</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]5</SPAN></SPAN>
[/TD]
[TD]5</SPAN></SPAN>
[/TD]
[TD]ILO No. 92 Crew Accommodation</SPAN></SPAN>
[/TD]
[TD]Yes</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]6</SPAN></SPAN>
[/TD]
[TD]6</SPAN></SPAN>
[/TD]
[TD]Flag Administration Regulations</SPAN></SPAN>
[/TD]
[TD]Yes</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]7</SPAN></SPAN>
[/TD]
[TD]7</SPAN></SPAN>
[/TD]
[TD]MLC2006</SPAN></SPAN>
[/TD]
[TD]Yes</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]8</SPAN></SPAN>
[/TD]
[TD]8</SPAN></SPAN>
[/TD]
[TD]OTHERS1</SPAN></SPAN>
[/TD]
[TD]Yes</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]9</SPAN></SPAN>
[/TD]
[TD]9</SPAN></SPAN>
[/TD]
[TD]Form 1424 Certificate of Fitness for the Carriage of Liquefied Gases in Bulk</SPAN></SPAN>
[/TD]
[TD]Yes</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]10</SPAN></SPAN>
[/TD]
[TD]10</SPAN></SPAN>
[/TD]
[TD]Form 1631 International Certificate of Fitness for the Carriage of Liquefied Gases in bulk</SPAN></SPAN>
[/TD]
[TD]Yes</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]11</SPAN></SPAN>
[/TD]
[TD]11</SPAN></SPAN>
[/TD]
[TD]Form 1632 Certificate of Fitness for the Carriage of Liquefied Gases in Bulk</SPAN></SPAN>
[/TD]
[TD]Yes</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]12</SPAN></SPAN>
[/TD]
[TD]12</SPAN></SPAN>
[/TD]
[TD]Form 2220 International Certificate of Fitness for the Carriage of Liquefied Gases in Bulk</SPAN></SPAN>
[/TD]
[TD]Yes</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]13</SPAN></SPAN>
[/TD]
[TD]13</SPAN></SPAN>
[/TD]
[TD]ME-Form 1782 Cert/Stat/Doc of Compliance for Engine Air Pollution Prevention (EAPP)</SPAN></SPAN>
[/TD]
[TD]Yes</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]14</SPAN></SPAN>
[/TD]
[TD]14</SPAN></SPAN>
[/TD]
[TD]ME-Form 1788 International Engine Air Pollution Prevention (EIAPP)</SPAN></SPAN>
[/TD]
[TD]Yes</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]15</SPAN></SPAN>
[/TD]
[TD]15</SPAN></SPAN>
[/TD]
[TD]ME-Form 1788N International Engine Air Pollution Prevention (EIAPP)</SPAN></SPAN>
[/TD]
[TD]Yes</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]16</SPAN></SPAN>
[/TD]
[TD]16</SPAN></SPAN>
[/TD]
[TD]ME-Form LR1782N SoC for Engine International Air Pollution Prevention (EAPP)</SPAN></SPAN>
[/TD]
[TD]Yes</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]17</SPAN></SPAN>
[/TD]
[TD]17</SPAN></SPAN>
[/TD]
[TD]AE-Form 1782 Cert/Stat/Doc of Compliance for Engine Air Pollution Prevention (EAPP)</SPAN></SPAN>
[/TD]
[TD]Yes</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]18</SPAN></SPAN>
[/TD]
[TD]18</SPAN></SPAN>
[/TD]
[TD]AE-Form 1788 International Engine Air Pollution Prevention (EIAPP)</SPAN></SPAN>
[/TD]
[TD]Yes</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]19</SPAN></SPAN>
[/TD]
[TD]19</SPAN></SPAN>
[/TD]
[TD]AE-Form 1788N International Engine Air Pollution Prevention (EIAPP)</SPAN></SPAN>
[/TD]
[TD]Yes</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]20</SPAN></SPAN>
[/TD]
[TD]20</SPAN></SPAN>
[/TD]
[TD]AE-Form LR1782N SoC for Engine International Air Pollution Prevention (EAPP)</SPAN></SPAN>
[/TD]
[TD]Yes</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
</TBODY>[/TABLE]

Thank you in advance!</SPAN>
Krasimir</SPAN>
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
Maybe something like this you can modify to suit your requirement

Code: 
Sub HideBlankRows()

Sheets("Sheet1").Select

For i = 13 To 300

    Range("B" & i).Select
    If Range("B" & i).Value = "" Then
            Rows(i & ":" & i).Select
            Selection.EntireRow.Hidden = True
    Else
            Selection.Offset(1, 0).Select
    End If

Next i

End Sub

And to Unhide the Rows

Code: 
Sub UnHideBlankRows()

Sheets("Sheet1").Select

For i = 13 To 300

    Range("B" & i).Select
    If Range("B" & i).Value = "" Then
             Rows(i & ":" & i).Select
            Selection.EntireRow.Hidden = False
    Else
            Selection.Offset(1, 0).Select
    End If

Next i

End Sub
 
Upvote 0
Hi Benders,

Thank you for the prompt reply!

Please be informed that the "HideBlankRows" code is working only when I RUN it but I would like this function to be automatic i.e whenever B13:B300 cell value is "" the respective rows to be automatically hidden without the need to run the macro.

The second code "UnHideBlankRows" also works when I RUN it but it unhide not only the rows where the B13:B300 cell value is other than "" but all of them from 13 to 300. I would also like this function to be automatic without the necessity to RUN the macro and when the cell value B13:B300 is other than "", the respective row to appear automatically.


Many Thanks,


Krasimir </SPAN>
 
Upvote 0
On Sheet1 first hide all the rows from 13 to 300. Then paste this code in the VBE of Sheet1.

Code: 
Private Sub Worksheet_activate()
Dim Target As Range
 
Sheets("Sheet1").Select
For i = 13 To 300
        Range("B" & i).Select
        If Range("B" & i).Value = "" Then
            Rows(i & ":" & i).Select
            Selection.EntireRow.Hidden = True
            Range("B" & i).Select
    Else
            If Range("B" & i).Value <> "" Then
            Rows(i & ":" & i).Select
            Selection.EntireRow.Hidden = False
            Range("B" & i).Select
    End If
    End If
Next i
End Sub

Apologies due to Time constraints I have not been able to test this. the code gets activated when you select Sheet1 so after every change in Sheet2 if you go back to Sheet1 this should hide/unhide relevant rows
 
Last edited:
Upvote 0
Thank you for your reply Benders,

I did as per your instruction but I found out malfunction.

When I add 30 items in Sheet2 and go back to Sheet1 it takes about 3 min for recalculation untill the added items appear and in the formula bar I see how it recalculates row by row.

When I remove 30 items from Sheet2 and go back to Sheet1 the rows do not disappear and in the B column cells I see =IF(Sheet2!#REF!="","",Sheet2!#REF!)

Best Regards,
Krasimir
 
Upvote 0
Could it be that since you delete the rows from Sheet2 instead of just clearing contents the #REF appears in Sheet1?
 
Upvote 0
Hi Benders,

Yes you right! I deleted the entire rows, I did not clear the contents!

Now I tried clearing the contents and it works!

Is there other way with higher speed of calculation?

Best Regards,
Krasimir
 
Upvote 0
Hi Benders,

Please accept my apologies for any inconvenience caused due to my query!

Please note that my Excel knowledge is very limited and this is the reason I might ask stupid questions or something which can't be done.

Have a nice day and many thanks again!

Krasimir
 
Upvote 0
No Need for Apologies my friend. In fact I feel helpless for not being able to help you any further. I am sailing in the same boat as you. :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Code to use multiple trigger cells to hide / unhide rows of corresponding question sets
Replies
1
Views
1K
dmt32
D
C
  • Question Question
Hiding Rows based on Cell Value- Multiple Use Document
Replies
5
Views
696
Christine Hansen
C

Forum statistics

Threads
1,223,243
Messages
6,170,964
Members
452,371
Latest member
Frana

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