Help with a hide row macro (not using filter)

[ANDRE.TASSEL]

Can anyone help with this macro ?

I need it to hide any rows which have a value of 0 in cols A to H

I DON'T want to use filters.

Every time I run it, nothing is hidden

Thanks in advance



Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Sheet1")
For rw = 1 To 300
If Application.WorksheetFunction.CountA( _
.Cells(rw, 1).Range("A1:H1")) = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut
.Range("A1:A300").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub

 

[Don Guillett]

Using that method, try it this way
Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Sheet1")
For rw = 1 To 300
If Application.CountA(Range(.Cells(rw, 1), .Cells(rw, 8))) = 0
Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut
.Range("A1:A300").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub

 

[ANDRE.TASSEL]

I'll give it a try tomorrow, thanks for the very quick reply

 

[Don Guillett]

Let us know

 

[JE McGimpsey]

One way:

If you really want to hide rows where cols A-H values are zero (not
blank):

Public Sub Hide_Print_Unhide()
Dim rCell As Range
Application.ScreenUpdating = False
With Sheets("Sheet1")
For Each rCell In .Range("A1:A300")
rCell.EntireRow.Hidden = Application.CountIf( _
rCell.Resize(1, 8), 0) = 8
Next rCell
.PrintOut Preview:=True
.Range("A1:A300").EntireRow.Hidden = False
End With
Application.ScreenUpdating = True
End Sub

 

[JE McGimpsey]

I think that should do the same thing as the OP's routine. If the OP
isn't seeing any rows hidden, it's likely that he has either formula or
non-printing text (e.g, space characters) in the target rows, which
COUNTA() will see as non-blank.

 

[Bob Phillips]

The OP was testing the same range every row.

 

[JE McGimpsey]

Hmm... I get the same behavior for each of your routines.

The OP's

.Cells(rw, 1).Range("A1:H1")

and your

Range(.Cells(rw, 1), .Cells(rw, 8))

produce identical range references each time through the loop, right?

 

[Don Guillett]

As usual JE is correct

 

[ANDRE.TASSEL]

Thanks for the info guys,
One more thing .....what if I wanted to hide the row where the contents
of col H is 5000 (numeric) or say "total" (or in fact any string).
As you may guess I'm still trying to get to grips with these types of
macros.

Once again thanks in advance

 

[Don Guillett]

another way to hide all rows with a length >0 in col A
Sub hr()
For i = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
If Len(Cells(i, 1)) > 0 Then Rows(i).Hidden = True
Next
End Sub