How can I use conditional formatting to hide or delete rows?
 

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?

How can I use conditional formatting to hide or delete rows?
 

"linda.beautiful.california"
. com wrote in message
...
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

How can I use conditional formatting to hide or delete rows?
 

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 DataFilterAutofilter
Use the dropdown on column A to select Non Blanks

--
Regards

Roger Govier


"linda.beautiful.california"
. com wrote in message
...
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?

How can I use conditional formatting to hide or delete rows?
 

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
. com 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?

How can I use conditional formatting to hide or delete rows?
 

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 DataFilterAutofilter
Use the dropdown on column A to select Non Blanks

--
Regards

Roger Govier


"linda.beautiful.california"
. com wrote in message
...
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?

How can I use conditional formatting to hide or delete rows?
 

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 DataFilterAutofilter
Use the dropdown on column A to select Non Blanks

--
Regards

Roger Govier


"linda.beautiful.california"
. com wrote in message
...
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?

How can I use conditional formatting to hide or delete rows?
 

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?