Searching for blank cells?
Searching for blank cells?
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Searching for blank cells?

  1. #1
    New Member
    Join Date
    Apr 2002
    Location
    Carole
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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.

  2. #2
    Board Regular eliW's Avatar
    Join Date
    Mar 2002
    Posts
    1,919
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


  3. #3
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,240
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

  4. #4
    Board Regular eliW's Avatar
    Join Date
    Mar 2002
    Posts
    1,919
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,240
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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.
    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com