Help with a hide row macro (not using filter)

[[email protected]]

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

--
Don Guillett
SalesAid Software
[email protected]
<[email protected]> wrote in message
news:[email protected]...
> 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
>

[[email protected]]

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

[Don Guillett]

Let us know

--
Don Guillett
SalesAid Software
[email protected]
<[email protected]> wrote in message
news:[email protected]...
> I'll give it a try tomorrow, thanks for the very quick reply
>

[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



In article <[email protected]>,
[email protected] 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

[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.



In article <[email protected]>,
"Don Guillett" <[email protected]> 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

[Bob Phillips]

The OP was testing the same range every row.

--
HTH

Bob Phillips

"JE McGimpsey" <[email protected]> wrote in message
news:[email protected]...
> 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 <[email protected]>,
> "Don Guillett" <[email protected]> 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

[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?


In article <#[email protected]>,
"Bob Phillips" <[email protected]> wrote:

> The OP was testing the same range every row.

[Don Guillett]

As usual JE is correct

--
Don Guillett
SalesAid Software
[email protected]
"JE McGimpsey" <[email protected]> wrote in message
news:[email protected]...
> 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 <#[email protected]>,
> "Bob Phillips" <[email protected]> wrote:
>
> > The OP was testing the same range every row.

[[email protected]]

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

--
Don Guillett
SalesAid Software
[email protected]
<[email protected]> wrote in message
news:[email protected]...
> 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
>