Macro to delete rows based on criteria in 2 maybe 3 fields

[russellh79]

I am trying to delete records where column 18 = "000000" and column 20 = "000000
I am using the following macro I copied from the web

Dim r As Long
Application.ScreenUpdating = False
For r = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
If Cells(r, 18) = "000000" And Cells(r, 20) = "000000" Then Rows(r).Delete
Next r
Application.ScreenUpdating = True
End Sub

I have been told that there is a better way to loop it because the method above would stop if there happen to be blank rows. (Which should never be the case.)

The problem is that this macro is deleting more than the 7 rows it should be deleting. Any idea on a fix?
*note This is actually a small part of a very large project. Really what I would like to do is Cut/Paste these records to a new sheet in the workbook called deleted. After I do that then I need to Cut/Paste duplicates into the same sheet called deleted"
1.) I am cutting and pasting the rows with "000000" in the 2 cells because they are actually copies (but not really copies) of records that exist with the proper numbers in those fields. So I want to remove all the rows where
a) Column 1 exists twice
b)And Column 18 and 20 both equal "000000"
2.) Cut and Paste Duplicates into a sheet because I am getting records that have Identical records for every field.
3.) By putting these into another sheet(s) I can check to make sure it is working properly.
4.) I want this to make it as auto as possible once set up. This spread sheet is created by exporting a large query (25k records returned) from an even larger database. So I export the results from Access and then I am manually performing this. Eventually the goal is to roll all of this up in a powershell program that will do it all. My limited knowledge will put that off for a while.



All Help very appreciated!!

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[russellh79]

Ok. I see what the problem is. Both macros delete the same number of records. There was another issue.

How would I add the argument to delete the row if
Column A font color is red and col R and T = "000000"?

[JOHN H. DAVIS]

Try:

Please Login or Register  to view this content.

[russellh79]

The recorded marco used to mark the fields red is
With Selection.FormatConditions(1).Font
.Color = -16383844
.TintAndShade = 0
Should I replace .Font.ColorIndex = 3 with .FontColor = -16383844 ?

[JOHN H. DAVIS]

If your font color is created using conditional formatting then VBA can't evaluate the color. I assumed it was manually done. That part of the code won't work.

[russellh79]

I snatched that code from VBA inside excel. You sure it won't work?

Here is the entire code.
Sub mark_duplicates()
'
' mark_duplicates Macro
' This will Highlight Duplicates and move them to the top.
'

'
Columns("A:A").Select
Selection.FormatConditions.AddUniqueValues
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).DupeUnique = xlDuplicate
With Selection.FormatConditions(1).Font
.Color = -16383844
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
ActiveWorkbook.Worksheets("Doug Test 2").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Doug Test 2").Sort.SortFields.Add(Range("A2:A23462") _
, xlSortOnFontColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(156, _
0, 6)
With ActiveWorkbook.Worksheets("Doug Test 2").Sort
.SetRange Range("A1:AH23462")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

[JOHN H. DAVIS]

I couldn't tell. I tried to set the font color based on the code you provided and couldn't. Keep in mind that I'm using 2003 though. So if that is the cause, I think someone else may have to help you.

[russellh79]

You have been a great help.
Basically the color was just highlighting duplicate cells.
So out of the records marked as duplicates did I want to delete the rows where R and T = "000000"
The record was marked as a duplicate if the Field A was represented more than once.
Is there a way to do that?

[JOHN H. DAVIS]

Will Column A be sorted?

[russellh79]

It can be but doesn't have too.

[JOHN H. DAVIS]

Can you provide a worksheet with some sample data?

[russellh79]

I can't. It contains customer information. Colum A is customer number. I have been running conditonal formating to flag duplicates in column A. The I sort data to put cells with font color at the top. (just so i can see em)
I have some records that are identical except the 2 columns are 0's in the duplicate. So i am trying to delete those.

After that is complete that only leaves duplicates (highlighted with font color red in conditonal formatting) where all columns are identical and I can just hit remove duplicates to remove the first copy of that record.

Dammit. I just answered my question. Sort em so the first duplicate shows the higher value and then remove duplicates will remove the appropriate one!!!

[G e r a r d]

If you want to know the used font color select cel and run code:

Please Login or Register  to view this content.

[JOHN H. DAVIS]

I sense that you've found a solution. Glad to hear that.