Selecting entire row for special cells
Posted by Wowblagger on November 23, 1999 6:12 AM
I am trying to hide all rows where cell values are empty. Rather than looping though each row, I am using the special cells method, as below:
Sheets("Sheet1").Activate
'RANGE("a1:a7").SpecialCells(xlBlanks).Interior.Color = RGB(255, 0, 0)
This works fine, changing the cell colour to red for those cells which are empty. But when using the entirerow property for the same range as below:
'RANGE("a1:a7").SpecialCells(xlBlanks).EntireRow.Hidden = True
This simply hides all rows in the specified range.
The data is the same in both cases, I am comparing like with like.
Is there an elegant way round this?
Posted by Mark on November 23, 1999 9:43 AM
Your method looked OK, so I tried it, even expanding it to multi column table, and it worked. The macro used is shown below. Try a copy and past into your module, changing the range to what you want.
Sub blanker()
'This macro adds a red background to each blank cell in the table
' in B3:I10, and then hides the row(s) containing the blank cell(s).
Sheets("Sheet1").Activate
Range("b3:i10").SpecialCells(xlBlanks).Interior.Color = RGB(255, 0, 0)
Range("b3:i10").SpecialCells(xlBlanks).EntireRow.Hidden = True
End Sub
Posted by Ivan Moala on November 23, 1999 9:57 PM
OR try;
Sub blanker()
Dim Blk As Range
On Error Resume Next
Set Blk = Range("A1:A7").SpecialCells(xlBlanks)
If Blk Is Nothing Then Msgbox "No Blanks":End
With Blk
.Interior.ColorIndex = 3 'Red
.EntireRow.Hidden = True
End With
End Sub
The on Error Resume Next is needed to trap
for an empty defined Object incase the select
blanks function finds no empty cells.
This is good practice for ANY routine where you
may have to dynamically search a range/object.
If you KNOW the range is Not empty then it is OK.
BUT JIC (Just in case)
Ivan
Posted by Bob Sovers on December 15, 1999 1:24 PM
If your intent is to hide all row where the entire row is empty, then the following code will work:
Sub HideAllEmptyRows()
Dim i As Long, fRow As Long, lRow As Long
fRow = ActiveSheet.UsedRange.Row
lRow = fRow + ActiveSheet.UsedRange.Rows.Count - 1
If fRow > 1 _
Then Range(Cells(1, 1), Cells(fRow - 1, 1)).EntireRow.Hidden = True
For i = fRow To lRow
If Application.WorksheetFunction.CountA(Cells(i, 1).EntireRow) = 0 _
Then Cells(i, 1).EntireRow.Hidden = True
Next i
If lRow < 65536 _
Then Range(Cells(lRow + 1, 1), Cells(65536, 1)).EntireRow.Hidden = True
End Sub
