Deleting rows with text submitted (Delete rows with purged

[JEHalm]

I am having some troubles with the code below. This code work very well when I was deleting rows <> 5.


'Deletes all Rows that <> 5


Dim x As Integer

For x = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).Rows.Count To 2 Step -1

If Cells(x, 1).value <> "5" Then Cells(x, 1).EntireRow.Delete Shift:=xlUp

Next x


This is a large spreadsheet going from A1 to AU15000. I'm deleting rows in column AT1:AT that have <> "submitted"...deleting rows containing "purged"


However, when I replace the values with text (purged or submitted) I have no success:


If Cells(x, 1).value <> "purged" ...all of the rows are deleted. There are only two words in the column (AU1:AU15000). They are either "purged" or "submitted".


I've used text instead of 'value' and I have tried 'text'. Maybe I should declare x as Variant?


I'm fairly certain that the value/format is incorrect.


---Current Code---


'Deletes all Rows that <> "submitted"

Dim x As Integer

For x = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).Rows.Count To 2 Step -1

If Cells(x, 1).Text <> "submitted" Then Cells(x, 1).EntireRow.Delete Shift:=xlUp

Next x

Any indeas?

 

[Hui]

JEHalm

The code below Current Code works fine

I would check that the word "submitted" doesn't have extra spaces/capitalization issues

maybe change a single line

If LCase(Trim(Cells(x, 1).Text)) <> "submitted" Then Cells(x, 1).EntireRow.Delete Shift:=xlUp


The definition of x as Integer is correct as it is counting backwards from Row(x) to 2

Just in case you have more than 32 rows of data you could make x a long


Dim x as Long

As Integers only go up to 32768


Also in your Post you state "There are only two words in the column (AU1:AU15000)"

your code is checking Column A not AU

Adjust accordingly

 

[JEHalm]

Hi Hui. I always approciate your help. I think I gave you the incorrect code to edit. The actual location of the column is AT. Cells in this column are either "purged" or "submitted". Every time I use the code below ALL of the 15,000 rowns of data are deleted. I want to keep the rows that display "submitted". My guess is that my range are incorrect. Please let me know how I have errored.


Dim x As Variant

For x = Range("AT1:AT" & Range("AT" & Rows.Count).End(xlUp).Row).Rows.Count To 2 Step -1

If Cells(x, 1).Value <> 5 Then Cells(x, 1).EntireRow.Delete Shift:=xlUp

Next x

 

[prasaddn]

Hi JEHalm,


As suggested by Hui, you may have to change two things:


1. the x declaration as long,

2. Important one, Cells(x, 1).value should be made as Cells(x, 46).value, because 1 is referring to column 1, which is A and not AT, and 46 indicates column AT.


Regards,

Prasad