Delete Rows Marked with "X"

[amerifax]

I'm trying to delete rows that have an "X" in one column. I tried setting a filter and than using F5-Special-Visible Cells Only. The problem is if I try to many at one time Excel chocks. There's also the potential problem of not doing the F5-Special-Visible Cells Only right, over and over again, and trashing records.

I was hoping that there is a formula I could run since formulas such as:
=COUNTIF($AG$2:$AG$100916,$AG2)
can run through 1,004,500 records with no chock.

Thanks for taking the time to read.
Bob

 

[VoG]

Try this with a copy of your sheet

Sub Del_X()
With Columns("AG")
    .AutoFilter field:=1, Criteria1:="X"
    .Resize(Rows.Count - 1).Offset(1).EntireRow.Delete
End With
ActiveSheet.AutoFilterMode = False
End Sub

 

[amerifax]

>VoG<
I did try and it deleted all the records. All the "X's" but also the ones I needed to Keep. Fears not it was BackedUp. I'm including the code. The only change I made to the code was the column from "AG" to "O", the column that contains an "X" which is my indicator for deletion. The column O "X" is only good if column L has a value of "4.3". In other words:

If O2:O1009679 = "X"
and L2:L1009679 is = "4.3" (Record should be deleted)

Basically:
1. If I sorted by L Largest to Smallest & N A to Z
2. (GO) to A515245 and set F%\ Special\Visual Cells Only & set filter to "X"
3. Delete

Problem is system, Excel, chocks.

I hope I explain myself. And thanks for the help.
Bob

 

[wigi]

I repaired the code of VoG: the code below will do what you want.

Sub Del_X()
    With [A1].CurrentRegion
        .AutoFilter 12, "4.3"
        .AutoFilter 15, "X"
        .Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With
    ActiveSheet.AutoFilterMode = False
End Sub

 

[amerifax]

Code
Sub Del_X()
With Columns("O")
.AutoFilter field:=1, Criteria1:="X"
.Resize(Rows.Count - 1).Offset(1).EntireRow.Delete
End With
ActiveSheet.AutoFilterMode = False
End Sub

 

[wigi]

Did you try my code already?

Can you please use code tags when you post code to the forum? Thanks.

 

[amerifax]

Excel claims to complex and I should Use?
1. Use data that can be selected in one contiguous rectangle. or
2. Use date from the same sheet.

Note: if I sort by:
"L" largest to smallest
Go to 515245
filtered by "X" of the remaining records would have the X.

I have tried:
The problem is F5\special\visible cells only
Select all and Delete. I get the same message. There are too many records.

 

[Boller]

Assuming that it is not a problem to sort the data, first sort by the column containing "X" and then by the "4.3" column so as to group all the rows to be deleted together .
Then filter for "X"/"4.3" and delete via SpecialCells/Visible.

 

[amerifax]

>>Boller<<
This I tried but I get the above error message.
Thought: Is there a way I can run a macro adjusting the range size as I experiment to find a range that works.

If I had a macro and could adjust the range it would be more methodical. Problem with useing the following:
F5\ Special\Visual Cells Only & set filter to "X"

It could create a problem if I zigged when I should have zagged. I did mess up records this way. Thank god for backups.
Bob

 

[Boller]

>>Boller<<
This I tried but I get the above error message.
Thought: Is there a way I can run a macro adjusting the range size as I experiment to find a range that works.

If I had a macro and could adjust the range it would be more methodical. Problem with useing the following:
F5\ Special\Visual Cells Only & set filter to "X"

It could create a problem if I zigged when I should have zagged. I did mess up records this way. Thank god for backups.
Bob

I don't understand any of your reply.

I see no reason why the method I suggested should not work.

It first groups all of the rows to be deleted together in one contiguous range.
This removes the problem you have had of exceeding the limit for the number of areas in the range. (Another way to avoid it is to upgrade from XL2003 to a later version. )