Searching for blank cells?

[Carole]

Is there a function key or a quick way to find a row or rows within a spreadsheet that have no data in any of it's cells at all. We want those rows to be highlighted or we need to know those rows. Rather than searching through a rather large set of data.

[eliW]

On 2002-04-17 09:09, Carole wrote:
Is there a function key or a quick way to find a row or rows within a spreadsheet that have no data in any of it's cells at all. We want those rows to be highlighted or we need to know those rows. Rather than searching through a rather large set of data.

Carole,
You can use conditional formatting:

Select first row
Conditional formate it by entering the formula: =COUNTA(1:1)=0
Choose the format, e.g. green color
Copy the format to all rows.

Only rows with no data will be painted
Eli

[Damon Ostrander]

Hi Carole,

I don't know of any built-in capability to highlight empty rows, so here is a macro that will do this:

Sub HiliteEmptyRows()
'Highlights empty rows in active worksheet in yellow
Dim Row As Range
For Each Row In Range(Rows(1), Rows(ActiveSheet.UsedRange.Rows(1).Row + _
ActiveSheet.UsedRange.Rows.Count - 1))
If IsEmpty(Cells(Row.Row, 1)) Then
With Cells(Row.Row, 1).End(xlToRight)
If .Column = 256 And IsEmpty(.Value) Then
Row.Interior.ColorIndex = 6
End If
End With
End If
Next Row
End Sub

If you would rather the macro just select the empty rows to highlight them, then use this macro instead:

Sub SelectEmptyRows()
'Selects empty rows on active worksheet
Dim Row As Range
Dim EmptyRows As Range
For Each Row In Range(Rows(1), Rows(ActiveSheet.UsedRange.Rows(1).Row + _
ActiveSheet.UsedRange.Rows.Count - 1))
If IsEmpty(Cells(Row.Row, 1)) Then
With Cells(Row.Row, 1).End(xlToRight)
If .Column = 256 And IsEmpty(.Value) Then
If EmptyRows Is Nothing Then
Set EmptyRows = Row
Else
Set EmptyRows = Union(EmptyRows, Row)
End If
End If
End With
End If
Next Row

If Not EmptyRows Is Nothing Then EmptyRows.Select

End Sub

To install these macros in your workbook, just use Alt-TMV and paste the code into the VBE code pane that appears. To run either of them use Alt-TMM, select the desired macro, and click Run.

[eliW]

On 2002-04-17 10:07, Damon Ostrander wrote:
Hi Carole,

I don't know of any built-in capability to highlight empty rows

Hi Damon,
Is something wrong with conditional formatting?
Regards,
Eli

[Damon Ostrander]

Hi eliW,

Conditional Formatting is a great way to do this without having to use VBA. I posted my answer before I saw yours--I probably wouldn't have bothered to post mine if I had seen your simple solution.

There is one advantage to the macro approach, however. If you have a LOT of data, and therefore a LOT of cells that would end up containing conditional formats, the macro approach could result in a much smaller file. Conditional formats require memory space. The macro approach also has the capability to hightlight the rows by selecting them (the second macro in my post), making the highlighting temporary, if this is desired in a particular situation.