HIDING ROWS

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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)




ANDREWS said:
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
Click to expand...
rows 20 to 150, provided one of the cells in the 20 columns has a value in it either a number or text.
 
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
 
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
 
Back
Top