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.:help

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. :banghead:

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!!!!!.:bow:

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

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!!!.
:bow: :bow: :bow:

best regards,
Pedro
:beerchug:
.
.

[johnske]

:thumb glad to be able to help