I need to hide empty rows

Col 1 has the date, Col 2 may/may not be empty

This works:



Sub Testing()

Count = 1

While Count < 20

If Cells(Count, 2) = "" Then

Rows(Count).EntireRow.Hidden = True

Count = Count + 1

End If

Wend

End Sub



I now need to make sure every cell in Cols 2-10 is empty before hiding the
row - of course this does not work:



If Range(Cells(Count, 2), Cells(Count, 10)) = "" Then



How do I test Cols 2-10 without a 'if' for each cell?



Thanks



Camlad

You could use the following test.....

If Application.WorksheetFunction.CountA(Range(Cells(C ount, 2), Cells(Count,
10))) 0 Then

The only thing to note that any cell with one or more spaces would look
empty but isn't so the test would fail, if you were testing for numerical
values only, use Count instead of CountA. You might like to rename your
control variable "Count" as it get a bit confusing!

--

Regards,
Nigel




"camlad" wrote in message
...
I need to hide empty rows

Col 1 has the date, Col 2 may/may not be empty

This works:



Sub Testing()

Count = 1

While Count < 20

If Cells(Count, 2) = "" Then

Rows(Count).EntireRow.Hidden = True

Count = Count + 1

End If

Wend

End Sub



I now need to make sure every cell in Cols 2-10 is empty before hiding the
row - of course this does not work:



If Range(Cells(Count, 2), Cells(Count, 10)) = "" Then



How do I test Cols 2-10 without a 'if' for each cell?



Thanks



Camlad

Many thanks - just what I wanted

Cheers from Camlad in Cambridge, England


"Nigel" wrote in message
...
You could use the following test.....

If Application.WorksheetFunction.CountA(Range(Cells(C ount, 2),
Cells(Count, 10))) 0 Then

The only thing to note that any cell with one or more spaces would look
empty but isn't so the test would fail, if you were testing for numerical
values only, use Count instead of CountA. You might like to rename your
control variable "Count" as it get a bit confusing!

--

Regards,
Nigel




"camlad" wrote in message
...
I need to hide empty rows

Col 1 has the date, Col 2 may/may not be empty

This works:



Sub Testing()

Count = 1

While Count < 20

If Cells(Count, 2) = "" Then

Rows(Count).EntireRow.Hidden = True

Count = Count + 1

End If

Wend

End Sub



I now need to make sure every cell in Cols 2-10 is empty before hiding
the row - of course this does not work:



If Range(Cells(Count, 2), Cells(Count, 10)) = "" Then



How do I test Cols 2-10 without a 'if' for each cell?



Thanks



Camlad