Help with a hide row macro (not using filter)
 

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

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

--
Don Guillett
SalesAid Software

wrote in message
ups.com...
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

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

Let us know

--
Don Guillett
SalesAid Software

wrote in message
ups.com...
I'll give it a try tomorrow, thanks for the very quick reply

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



In article . com,
wrote:

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

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.



In article ,
"Don Guillett" wrote:

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

The OP was testing the same range every row.

--
HTH

Bob Phillips

"JE McGimpsey" wrote in message
...
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.



In article ,
"Don Guillett" wrote:

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

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?


In article ,
"Bob Phillips" wrote:

The OP was testing the same range every row.

As usual JE is correct

--
Don Guillett
SalesAid Software

"JE McGimpsey" wrote in message
...
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?


In article ,
"Bob Phillips" wrote:

The OP was testing the same range every row.

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

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

--
Don Guillett
SalesAid Software

wrote in message
oups.com...
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