I want to use conditional formatting to hide or delete rows in Excel if the
cells in column A are blank. How can I do this or, if conditional formatting
won't work, what other way is there to accomplish this?

"linda.beautiful.california"
<(E-Mail Removed)> wrote in message
news:80DD8D81-A311-41C9-BFF7-(E-Mail Removed)...
>I want to use conditional formatting to hide or delete rows in Excel if the
> cells in column A are blank. How can I do this or, if conditional
> formatting
> won't work, what other way is there to accomplish this?

Auto-filter
Select blanks & delete the rows.
--
David Biddulph

Hi Linda

Conditional formatting will not hide rows or delete rows.
You could use conditional formatting to make the font colour equal the
background colour, so the data would appear to be "hidden", but the row
will still show.

Why not mark your header row and Data>Filter>Autofilter
Use the dropdown on column A to select Non Blanks

--
Regards

Roger Govier


"linda.beautiful.california"
<(E-Mail Removed)> wrote in message
news:80DD8D81-A311-41C9-BFF7-(E-Mail Removed)...
>I want to use conditional formatting to hide or delete rows in Excel if
>the
> cells in column A are blank. How can I do this or, if conditional
> formatting
> won't work, what other way is there to accomplish this?

Conditional formatting cannot hide or delete rows, although you could use CF to
color the font white in the rest of the cells in the row.

No formula or Function can hide or delete rows.

You would need VBA code to hide or delete rows.

Sub delete_rows()
Dim RowNdx As Long
Dim LastRow As Long
LastRow = ActiveSheet.UsedRange.Rows.Count
For RowNdx = LastRow To 1 Step -1
If Cells(RowNdx, "A").Value = "" Then
' Rows(RowNdx).Delete
Rows(RowNdx).EntireRow.Hidden = True
End If
Next RowNdx
End Sub


Gord Dibben MS Excel MVP


On Thu, 6 Jul 2006 09:18:02 -0700, linda.beautiful.california
<(E-Mail Removed)> wrote:

>I want to use conditional formatting to hide or delete rows in Excel if the
>cells in column A are blank. How can I do this or, if conditional formatting
>won't work, what other way is there to accomplish this?

Making font color same as background "hides" the text on the screen,
but depending on colors chosen (e.g. white on white), it may be visible
when it prints.

Use custom number format of ";;;" to hide text on display and print

Roger Govier wrote:
> Hi Linda
>
> Conditional formatting will not hide rows or delete rows.
> You could use conditional formatting to make the font colour equal the
> background colour, so the data would appear to be "hidden", but the row
> will still show.
>
> Why not mark your header row and Data>Filter>Autofilter
> Use the dropdown on column A to select Non Blanks
>
> --
> Regards
>
> Roger Govier
>
>
> "linda.beautiful.california"
> <(E-Mail Removed)> wrote in message
> news:80DD8D81-A311-41C9-BFF7-(E-Mail Removed)...
> >I want to use conditional formatting to hide or delete rows in Excel if
> >the
> > cells in column A are blank. How can I do this or, if conditional
> > formatting
> > won't work, what other way is there to accomplish this?

Of course! It is as simple as that. Thank you Roger.

I've elaborated a little on this since your solution does not provide an
automatic update - which was what I needed. Here's where this SHORT macro,
placed in the worksheet in question, comes in handy:

Private Sub Worksheet_Activate()
Selection.AutoFilter Field:=1, Criteria1:="<>"
End Sub

Does the trick.

Kind regards,
Cooz


"Roger Govier" wrote:

> Hi Linda
>
> Conditional formatting will not hide rows or delete rows.
> You could use conditional formatting to make the font colour equal the
> background colour, so the data would appear to be "hidden", but the row
> will still show.
>
> Why not mark your header row and Data>Filter>Autofilter
> Use the dropdown on column A to select Non Blanks
>
> --
> Regards
>
> Roger Govier
>
>
> "linda.beautiful.california"
> <(E-Mail Removed)> wrote in message
> news:80DD8D81-A311-41C9-BFF7-(E-Mail Removed)...
> >I want to use conditional formatting to hide or delete rows in Excel if
> >the
> > cells in column A are blank. How can I do this or, if conditional
> > formatting
> > won't work, what other way is there to accomplish this?
>
>
>

You have to "think outside the box" (pun intended)... Instead of trying to
make the borders thick when your conditional formatting (CF) condition is
TRUE, make all the cell borders thick and set the CF to make them thin (or
dotted, etc.) when your condition is FALSE. Another way is to make all the
cell borders the finest dotted line (which, when printed, looks a solid line
that's thinner than the regular solid line) and have the CF change them to
the regular solid line when your condition is TRUE. I hope this makes sense.

"linda.beautiful.california" wrote:

> I want to use conditional formatting to hide or delete rows in Excel if the
> cells in column A are blank. How can I do this or, if conditional formatting
> won't work, what other way is there to accomplish this?