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>
 
Sort by date Sort by votes
Try this:
Code: 
Option Explicit
Private Sub Worksheet_activate()
'Declare Variables
Dim shtSheet As Excel.Worksheet
Dim lngLastRow As Long
Dim lngCounter As Long
'Set the worksheet object :: Replace "Start" with your worksheet name
Set shtSheet = Workbooks("Start.xlsm").Worksheets(1)
'Disable screen updating to improve perfomance
Excel.Application.ScreenUpdating = False
'Find the last row with values in it.
With shtSheet
    If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
        lngLastRow = .Cells.Find(What:="*", After:=Range("A1"), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
    Else
        lngLastRow = 1
    End If
'Loop through the rows and identify those that have an empty value in column B
    For lngCounter = 1 To lngLastRow
        'If the value in column B is empty
        If .Range("B" & lngCounter).Value = "" Then
            'Hide the entire row
            .Rows(lngCounter & ":" & lngCounter).EntireRow.Hidden = True
        Else
            'Else, ensure that the entire row is visible
            .Rows(lngCounter & ":" & lngCounter).EntireRow.Hidden = False
        End If
    'Move to the next row
    Next lngCounter
End With
'Return screen updating to a normal state
Excel.Application.ScreenUpdating = True

'Clean up
Set shtSheet = Nothing
End Sub

Place this in the worksheet module for Sheet1 in the VBE. Make sure you remove any other code that you have previously used. This sub will work very fast for you

Let me know if you have any questions, thanks!
 
Upvote 0

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
CSturgis said:
Try this:
Code: 
Option Explicit
Private Sub Worksheet_activate()
'Declare Variables
Dim shtSheet As Excel.Worksheet
Dim lngLastRow As Long
Dim lngCounter As Long
'Set the worksheet object :: Replace "Start" with your worksheet name
Set shtSheet = Workbooks("Start.xlsm").Worksheets(1)
'Disable screen updating to improve perfomance
Excel.Application.ScreenUpdating = False
'Find the last row with values in it.
With shtSheet
    If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
        lngLastRow = .Cells.Find(What:="*", After:=Range("A1"), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
    Else
        lngLastRow = 1
    End If
'Loop through the rows and identify those that have an empty value in column B
    For lngCounter = 1 To lngLastRow
        'If the value in column B is empty
        If .Range("B" & lngCounter).Value = "" Then
            'Hide the entire row
            .Rows(lngCounter & ":" & lngCounter).EntireRow.Hidden = True
        Else
            'Else, ensure that the entire row is visible
            .Rows(lngCounter & ":" & lngCounter).EntireRow.Hidden = False
        End If
    'Move to the next row
    Next lngCounter
End With
'Return screen updating to a normal state
Excel.Application.ScreenUpdating = True

'Clean up
Set shtSheet = Nothing
End Sub

Place this in the worksheet module for Sheet1 in the VBE. Make sure you remove any other code that you have previously used. This sub will work very fast for you

Let me know if you have any questions, thanks!
Click to expand...

Hi CSturgis,

Thank you for the help!

Unfortunately the code does not work.
It gives "Run-time error '9': Subscript out of range."
Debug: Set shtSheet = Workbooks("Start.xlsm").Worksheets(1)

Just to make it sure that there is no misunderstanding, please note the following:

In Sheet2 B1:B300 is my database where the listed items are changing.

In Sheet1 B1:B300 I have simple formula =If(Sheet2!B1="","",Sheet2!B1)

I need in Sheet1 within the range B100:B300 macro to hide the respective rows where the cells have value = "" and to unhide the rows where the cells have value other than "".

Please be informed that the code of Benders given above is doing exactly what I need but it is just a bit slow ...

Many Thanks & Best Regards!
Krasimir
 
Upvote 0
Good morning,

My apologies for not being thorough, on the line
Code: 
Set shtSheet = Workbooks("Start.xlsm").Worksheets(1)
You will need to replace "Start.xlsm" with the name of your workbook. If the sheet that you would like this sub to operate on is the first sheet in your workbook, then Worksheet(1) can remain. If it is actually the second sheet, you would change it to .Worksheets(2).

Give that a try and let me know how it turns out for you.

Additionally, this sub looks for the last cell in column A that has a value in it, if that goes beyond your range of B100:B300 then we can modify the code in this code:
Code: 
For lngCounter = 1 To lngLastRow
to be...
Code: 
For lngCounter = 100 to 300
and we can remove the following lines:
Code: 
 If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
        lngLastRow = .Cells.Find(What:="*", After:=Range("A1"), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
    Else
        lngLastRow = 1
    End If

If you run into any further issues, let me know. This operation works nearly instantaneously. Thanks
 
Upvote 0
Hi CSturgis,

Please accept my apologies for not being thorough but actually I did not notice you have sent the above explanation with the code modification.
I have also gave up but returned again to the issue.
I would like to thank you very much for the modified code which works perfectly and it is very fast!!!


Kind Regards,

Krasimir
 
Last edited:
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,247
Messages
6,171,004
Members
452,374
Latest member
keccles

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