Searching for blank cells?

Carole

New Member
Joined
Apr 2, 2002
Messages
8
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.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top