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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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"
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?
Click to expand...

Auto-filter
Select blanks & delete the 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 Data>Filter>Autofilter
Use the dropdown on column A to select Non Blanks

--
Regards

Roger Govier


"linda.beautiful.california"
 
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
 
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
 
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
 
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.
 
Back
Top