Hiding Cells Automatically

H

Hartsell

Hello

I have data in rows 6 through 100. To make viewing and printing easy i would like to hide all the rows that have a zero value in columns C through O. I have never had any luck with macros but I would be willing to give it another shot if someone could help me write the code

It is possible that data can be updated in another worksheet which would update a value referenced in the macro. Bacause of that this would have to be an automatic process

Thanks in advanc
Brya
Bryan
 
F

Frank Kabel

Hi Bryan
try the following macro

Private Sub hide_row()
Dim r As Long
Application.ScreenUpdating = False
With Worksheets("Sheet1")
For r = .UsedRange.Rows(.UsedRange.Rows.Count).Row To 1 Step -1
If Application.CountA(Range(Cells(r,"C"),Cells(r,"O"))) = 0
Then
.Rows(r).Hidden = True
End If
Next
End With
Application.ScreenUpdating = True
End Sub
 
H

Hartsell

OK
Please don't think this is a stupid question, but are there certain areas in the formula that are meant as references

Like somewhere should i say "Start at row 5 and work down"? "Sheet 1" - is this the name of the sheet where the macro does its work ie "Totals"? The IF Application line is giving me back an error "Expected Then or GoTo". When i enter "Then" i get another Expected: line # or end of statment. So when you use things like "Range" and Cells" is that literal content or am i expected to enter some argument

This is probably why i always have problems, i just don't have the knowledge to know why what is where, and therefore get stuck

Bryan
 
F

Frank Kabel

Hi
your questions:
1. This works the entire workbook starting in row 1 two the last used
row. If you want it to start at a different row change the line
For r = .UsedRange.Rows(.UsedRange.Rows.Count).Row To 1 Step -1
to
For r = .UsedRange.Rows(.UsedRange.Rows.Count).Row To start_row_number
Step -1

2. Yes this macro will use sheet 1. If you want to run the macro on the
active sheet change the line
With Worksheets("Sheet1")
to
With ActiveSheet

3. To remove the error combine the two line
If Application.CountA(Range(Cells(r,"C"),Cells(r,"O"))) = 0
Then

into ONE signle line (wordwarpping from the newsreader causes this)
 
H

Hartsell

Frank

Here's the code

Private Sub Hide_Row(
Dim r As Lon
Application.ScreenUpdating = Fals
With Worksheets("Sheet2"
For r = .UsedRange.Rows(.UsedRange.Rows.Count).Row To 5 Step -
If Application.CountA(Range(Cells(r, "C"), Cells(r, "O"))) = 0 The
.Rows(r).Hidden = Tru
End I
Nex
End Wit
Application.ScreenUpdating = Tru
End Su

Is there some sort of step to activate it

Thank
Brya
Grafenwoehr, Germany
 
Top