Solved: Hide Rows...help in code

[parttime_guy]

Hi All,

The sheet has formulas from range A2:J100 (shows blanks whenever needed). Need to hide entire rows if any cell in the range is blank.

Why is this code not working

Sub refresh2()
Application.ScreenUpdating = False
Dim r As Range
Set myRange = ActiveSheet.Range("A2:A100")
For Each r In myRange
If r.Value <> 0 Then
r.EntireRow.Hidden = True
End If
Next
Application.ScreenUpdating = True
End Sub


When the above code runs (it totally does the reverse :banghead: it seems to hide all rows with values :think: )

Kindly help

Regards

[goobers]

Try replacing the <> with =

Sub refresh2()
Application.ScreenUpdating = False
Dim r As Range
Set myRange = ActiveSheet.Range("A2:A100")
For Each r In myRange
If r.Value = 0 Then
r.EntireRow.Hidden = True
End If
Next
Application.ScreenUpdating = True
End Sub

[Bob Phillips]

Sub refresh2()
Application.ScreenUpdating = False
Dim r As Range
Set myRange = ActiveSheet.Range("A2:A100")
For Each r In myRange
r.EntireRow.Hidden = IsEmpty(r.Value)
Next
Application.ScreenUpdating = True
End Sub

[parttime_guy]

Hi Guys,

Attached is a sample worksheet having sheets "Data" and "Summary"

I am trying automatically hide the columns in the summary where there are no "headings" - which has been achived.

Problem with rows
In the data sheet row 7 & 13 should not be hidden as data is present.

Kindly view/test the sample sheet.

Best Regards

[Teeroy]

Your sample code and statement of problem were inconsistent. Your problem said any cell in A2:J100, but your code was only checking A2:A100 (i.e. Client Names) for blanks. What you really want is to hide a row only if every cell in Columns A:J for that row is blank (and that can be either 0 or ""). So we need to reverse the premise and say the row should be hidden unless any data is present. The following will do that and hide any columns with no heading in one go.

Sub refresh3()

Dim myRange As Range
Dim r As Range

Application.ScreenUpdating = False
With Sheets("Summary")
'*****************Hide Rows with no data ***************
.Rows("2:100").Hidden = True
Set myRange = .Range("A2:J100")
For Each r In myRange
If r.Value <> 0 And r.Value <> "" Then
r.EntireRow.Hidden = False
End If
Next
'*****************Hide Columns with no Heading ***************
Columns("B:IV").Hidden = False
Set myRange = .Range("B1:IV1")
For Each r In myRange
If r.Value = 0 Or r.Value = "" Then
r.EntireColumn.Hidden = True
End If
Next
End With
Sheets("DATA").Select
Application.ScreenUpdating = True
End Sub

[parttime_guy]

Hi Teeroy,
Thanks for ur code:friends:
The hiding of the rows works fine on every update

Problem with the columns
It works the 1st time great if all the columns in the summary sheet are visible
But... when I update the data sheet, the summary sheet does not refresh automatically.
I have to manually unhide all the columns in the summary sheet and then update the the data sheet.
Any ideas :think:

[Teeroy]

Yes, the events are calling your old routines.
Delete your code in the worksheet change event for sheet "Data" .
Add the following to the "Summary" sheet code container.
Private Sub Worksheet_Activate()
Call refresh3
End Sub This has the added benefit of reducing processing overhead as you don't do it on every change in the "Data" sheet, only when you want to go to the "Summary" sheet to view the summary.

[parttime_guy]

Hey Teeroy,
Understood the problem - Thx for your help - ur code works great after making your suggested changes.:bow:

Just made one small change I commented the following line
Sheets("DATA").Select
As the code would not allow me to view the summary sheet it would just keep comming back to the data sheet:doh:
Best regards:beerchug:

[Teeroy]

Sorry, I made that change in the file but forgot to mention it.