Autohiding Empty Rows. VBA code

[pegbol]

.
.
Hello Masters,

I have 2 sheets - A Master Data - and a secondary worksheet which I use for do display certain cells / rows from the master.

I am trying to figure a way of autohiding rows within the secondary sheet that don't contain data.

So, say for example: I have the following formula in the secondary sheet (range B5:B305)

=IF(ISERROR(INDEX('Master'!F:F,MATCH(A5,'Master'!A:A,0))),"",INDEX('Master'!F:F,MATCH(A5,'Master'!A:A,0)))

If the formula returns a blank value, I need the entire row to be hidden automatically.


Your valuable help will be highly appreciated.

best regards,
Pedro
.
.

[johnske]

Hi Pedro,

See if this does what you want

Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim N&
Application.ScreenUpdating = False
For N = 5 To 305
If Range("B" & N).Value = 0 Then
Rows(N).EntireRow.Hidden = True
Else
Rows(N).EntireRow.Hidden = False
End If
Next N
Application.ScreenUpdating = True
End Sub

HTH,
John

[Steiner]

Maybe you could simply use the AutoFilterFeature and set it to display all non-empty cells?

[pegbol]

.
.
John, thanks so much for your kind solution.

I implemented the code, but can't make it work.

Please, would you (or anyone else) help me to resolve the problem ?.

I enclose my file with the precise data.

kindest regards,
Pedro
.
.

[johnske]

Hi Pedro,

Try this instead

Option Explicit

Private Sub Worksheet_Calculate()
    Dim N&
    Application.ScreenUpdating = False
    For N = 5 To 305
        If Range("B" & N).Text = Empty Then
            Rows(N).EntireRow.Hidden = True
        Else
            Rows(N).EntireRow.Hidden = False
        End If
    Next N
    Application.ScreenUpdating = True
End Sub

Regards,
John

[pegbol]

.
.
John,

Thanks so much!!!!!.

Yes!!!. Now, the code hides all empty rows.

One last question:
When I add new registers in the Master sheet, the code takes long time to update my secondary sheet.
Can be possible make it faster ?.
If not, it is OK. I could deal with that.

I appreciate your valuable assistance.

best regards,
Pedro
.
.

[johnske]

Hi Pedro,

You could change

Private Sub Worksheet_Calculate()

to

Private Sub Worksheet_Activate()

it then just runs the once each time the second sheet is activated

Regards,
John

[pegbol]

.
.
Again, thanks so much Master for all your kind attention!!!.


best regards,
Pedro

.
.

[johnske]

glad to be able to help