Delete entire row based on cell fill color

J

jamesdean1379

Board Regular
Joined
Jun 11, 2014
Messages
55
Hey everyone trying to get a VBA code that will delete entire row based on cell fill color. Basically what I have is a spreadsheet and certain cells are filled with grey color and i need to delete the entire row.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
jamesdean1379 said:
Hey everyone trying to get a VBA code that will delete entire row based on cell fill color. Basically what I have is a spreadsheet and certain cells are filled with grey color and i need to delete the entire row.
Click to expand...
How did the cells get their color... manually set or by means of Conditional Formatting?

Also, what is the Color or ColorIndex value of the gray color that is being used?
 
Upvote 0
Rick Rothstein said:
How did the cells get their color... manually set or by means of Conditional Formatting?

Also, what is the Color or ColorIndex value of the gray color that is being used?
Click to expand...

Rick,

I am not sure of the ColorIndex but the RGB is (192, 192, 192) or Gray -25%. They are manually applied
 
Upvote 0
jamesdean1379 said:
Rick,

I am not sure of the ColorIndex but the RGB is (192, 192, 192) or Gray -25%. They are manually applied
Click to expand...
Assuming the cells you want to process are in Column A, give this macro a try...
Code: 
Sub DeleteRowsContainingGrayCells()
  Application.FindFormat.Clear
  Application.FindFormat.Interior.Color = RGB(192, 192, 192)
  With Columns("[B][COLOR="#0000FF"]A[/COLOR][/B]")
    .Cells.Replace "*", "#N/A", SearchFormat:=True, ReplaceFormat:=False
    On Error Resume Next
    .SpecialCells(xlConstants, xlErrors).EntireRow.Delete
    On Error GoTo 0
  End With
  Application.FindFormat.Clear
End Sub
 
Upvote 0
Rick Rothstein said:
Assuming the cells you want to process are in Column A, give this macro a try...
Code: 
Sub DeleteRowsContainingGrayCells()
  Application.FindFormat.Clear
  Application.FindFormat.Interior.Color = RGB(192, 192, 192)
  With Columns("[B][COLOR=#0000FF]A[/COLOR][/B]")
    .Cells.Replace "*", "#N/A", SearchFormat:=True, ReplaceFormat:=False
    On Error Resume Next
    .SpecialCells(xlConstants, xlErrors).EntireRow.Delete
    On Error GoTo 0
  End With
  Application.FindFormat.Clear
End Sub
Click to expand...

Thanks Rick! works perfectly!
 
Upvote 0
How can this code be adjusted to also delete blank cells in Column A....I have a similar task...and the Macro works, deleting rows based on cell color. But it still leaves rows that contain blank cells (no value)

Rick Rothstein said:
Assuming the cells you want to process are in Column A, give this macro a try...
Code: 
Sub DeleteRowsContainingGrayCells()
  Application.FindFormat.Clear
  Application.FindFormat.Interior.Color = RGB(192, 192, 192)
  With Columns("[B][COLOR=#0000ff]A[/COLOR][/B]")
    .Cells.Replace "*", "#N/A", SearchFormat:=True, ReplaceFormat:=False
    On Error Resume Next
    .SpecialCells(xlConstants, xlErrors).EntireRow.Delete
    On Error GoTo 0
  End With
  Application.FindFormat.Clear
End Sub
Click to expand...
 
Upvote 0
ldashev said:
How can this code be adjusted to also delete blank cells in Column A....I have a similar task...and the Macro works, deleting rows based on cell color. But it still leaves rows that contain blank cells (no value)
Click to expand...
Give this a try (I highlighted the new row in red)...
Code: 
Sub DeleteRowsContainingGrayCells()
  Application.FindFormat.Clear
  Application.FindFormat.Interior.Color = RGB(192, 192, 192)
  With Columns("[B][COLOR="#0000FF"]A[/COLOR][/B]")
    .Cells.Replace "*", "#N/A", SearchFormat:=True, ReplaceFormat:=False
    On Error Resume Next
    .SpecialCells(xlConstants, xlErrors).EntireRow.Delete
    [B][COLOR="#FF0000"].SpecialCells(xlBlanks).EntireRow.Delete[/COLOR][/B]
    On Error GoTo 0
  End With
  Application.FindFormat.Clear
End Sub
 
Upvote 0
That didn't work for me? Maybe another factor? Another idea I have is can you change the macro to look for a fill color or blank cell and delete based on that?

Rick Rothstein said:
Give this a try (I highlighted the new row in red)...
Code: 
Sub DeleteRowsContainingGrayCells()
  Application.FindFormat.Clear
  Application.FindFormat.Interior.Color = RGB(192, 192, 192)
  With Columns("[B][COLOR=#0000FF]A[/COLOR][/B]")
    .Cells.Replace "*", "#N/A", SearchFormat:=True, ReplaceFormat:=False
    On Error Resume Next
    .SpecialCells(xlConstants, xlErrors).EntireRow.Delete
    [B][COLOR=#FF0000].SpecialCells(xlBlanks).EntireRow.Delete[/COLOR][/B]
    On Error GoTo 0
  End With
  Application.FindFormat.Clear
End Sub
Click to expand...
 
Upvote 0
Or possibly another piece of logic that could work in my case is to say 'whenever a cell is Grey or RGB=192,192,192 keep it and instead delete all others. Kind of the opposite of this.
 
Upvote 0
ldashev said:
That didn't work for me? Maybe another factor? Another idea I have is can you change the macro to look for a fill color or blank cell and delete based on that?
Click to expand...
That is what my code does. Your blank cell... is it truly blank (no spaces or other hidden characters) or is there a formula in it that is displaying "" as its result?


ldashev said:
Or possibly another piece of logic that could work in my case is to say 'whenever a cell is Grey or RGB=192,192,192 keep it and instead delete all others. Kind of the opposite of this.
Click to expand...
I cannot adopt the code to do the negative like that, I would have to rewrite the entire macro. Lets see if we can solve the problem above first.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
VBA - Deleting Entire Row Based on a Condition
Replies
2
Views
500
jolivanes
jolivanes
H
  • Solved
Using Macro/VLOOKUP to return format of cell
Replies
9
Views
123
mumps
mumps
I
  • Solved
Delete row advice please
Replies
6
Views
210
ipbr21054
I
L
  • Solved
Fill in cell color with VBA
Replies
1
Views
148
NateSC
NateSC
J
  • Question
Fill a row without overwriting existing cell data
Replies
0
Views
75
jmichalski81
J

Forum statistics

Threads
1,215,043
Messages
6,122,822
Members
449,096
Latest member
Erald

Share this page

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