H
I am finding difficulties to write a macro to hide rows if all their cells are empty
I have a workbook with 500 rows and 20 columns. I would like to have the flexibility to print a given section of rows, say rows 20 to 150, provided one of the cells in the 20 columns has a value in it either a number or text.
Rows which have all cells empty across the 20 columns not to be printed.
Thanks

Try something like this
It will check all columns this example not 20.
I don't know if that is a problem???

Private Sub Test()
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(.Rows(r)) = 0 Then
.Rows(r).Hidden = True
End If
Next
End With
Application.ScreenUpdating = True
End Sub



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"ANDREWS" wrote in message ...
Hi
I am finding difficulties to write a macro to hide rows if all their cells are empty.
I have a workbook with 500 rows and 20 columns. I would like to have the flexibility to print a given section of rows, say
rows 20 to 150, provided one of the cells in the 20 columns has a value in it either a number or text.
Rows which have all cells empty across the 20 columns not to be printed.
Thanks

Thanks

It works. The problem is that my first column has data, something which i did not mentioned earlier sorry about this. So my problem still remains.

Thanks

Try this then for checking column 2 to 20

Sub Test()
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, 2), .Cells(r, 20))) = 0 Then
.Rows(r).Hidden = True
End If
Next
End With
Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"andrews" wrote in message ...
Thanks

It works. The problem is that my first column has data, something which i did not mentioned earlier sorry about this. So my
problem still remains.

Thanks

Hi andrews

I see I forgot a dot
Add a dot before Range to be sure it will use Worksheets("Sheet1")

If Application.CountA(.Range(.Cells(r, 2), .Cells(r, 20))) = 0 Then


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Ron de Bruin" wrote in message ...
Try this then for checking column 2 to 20

Sub Test()
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, 2), .Cells(r, 20))) = 0 Then
.Rows(r).Hidden = True
End If
Next
End With
Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"andrews" wrote in message ...
Thanks

It works. The problem is that my first column has data, something which i did not mentioned earlier sorry about this. So my
problem still remains.

Thanks

Thanks Ro
It works perfec
thanks agai
andrew