Hiding rows
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 |
Hiding rows
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 |
Hiding rows
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 |
All times are GMT +1. The time now is 10:38 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com