Delete using Filter

[amerifax]

I have many records in around 20% have an "x" in a column. I want to delete all the records that have an "X" in a column called Dup. so I used "Sort & Filter" displaying only the records that had an "X" in the Dup column. I'm sure you know what happened. I deleted even records that did not have an "X". So obviously, the filter did not work as I assumed it would.

What am I missing?

 

[VoG]

Try:

Filter

Press F5, click Special, check Visible cells only, click OK

Press Delete

 

[Sektor]

What is the range of table headers? For instance, A1: F1.

 

[amerifax]

>>Sektor<<
AC2:AC200746 Contains many records that are marked for deletion by indicating with an "X".
Thanks
Bob

 

[Sektor]

Sub RemoveAllXs()

    Dim rng As Range
    
    With Range("AC1")
        .AutoFilter Field:=1, Criteria1:="X"
        Set rng = Range("AC2:AC200746").SpecialCells(xlCellTypeVisible)
        If Not rng Is Nothing Then
            rng.EntireRow.Delete
        End If
        .AutoFilter 'Remove filter.
    End With

End Sub

 

[amerifax]

>>VoG<<

I would think I would first set the filter. As I did before, showing only the records that have the "X". And if I have this right. I would then use F5 set the range, then go into special and select visible cells only. Then highlight the far left column and delete by rowes.

Dragging the far left column to Mark is a bit of a pain. Is there a better way to activate a select delete.

Thanks Bob

>>Sektor<<
I think you have answered my question. I will run this in the morning.
Thanks
Bob

 

[amerifax]

Sektor,

I wanted to clarify a few things before I ran this code. Looking at the code it seems geared towards the column and I know I gave you that impression. There is another dynamic to the data I didn't mention. The full data file is actually made up of two files. The top half is 11.3 and the bottom half is 4.3. I only want to delete the rows marked with an "X" for the 4.3 records.

This might be all you need to consider:
4.3 Data - A100917:AD200746

Addition Info you might Need:
These are my ranges:
Header Row - A1:AD1
Full Data - A2:AD200746
11.3 Data - A2:AD100916
4.3/11.3 ID - AA
DUP - AC

I'm not sure how much if any this might change the code, but I thought I should clarify this before running.

Thanks for all your help so far!

Bob

 

[amerifax]

Try:

Filter

Press F5, click Special, check Visible cells only, click OK

Press Delete

This works for us, but we can only delete about 500 records at a time and we are dealing with about 1,000,000 records overall.

We also tried the code from Sektor, but it didn't work quite right, I think due to the range used.

I think there may be something simple we are missing.

Bob