HIDING ROWS

[ANDREWS]

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

 

[Ron de Bruin]

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)

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.

 

[andrews]

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

 

[Ron de Bruin]

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

 

[Ron de Bruin]

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

 

[andrews]

Thanks Ro
It works perfec
thanks agai
andrew