View Full Version : [SOLVED:] Autohiding Empty Rows. VBA code
pegbol
05-17-2005, 08:20 PM
.
.
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
05-17-2005, 09:09 PM
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
05-18-2005, 12:29 AM
Maybe you could simply use the AutoFilterFeature and set it to display all non-empty cells?
pegbol
05-18-2005, 05:27 AM
.
.
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
05-18-2005, 05:55 AM
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
05-18-2005, 08:10 AM
.
.
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
05-18-2005, 02:36 PM
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
05-18-2005, 03:53 PM
.
.
Again, thanks so much Master for all your kind attention!!!.
:bow: :bow: :bow:
best regards,
Pedro
:beerchug:
.
.
johnske
05-18-2005, 04:01 PM
:thumb glad to be able to help
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.