Delete rows in Excel based on cell text using VBA 1

[MDA]

Hi all,

I have a large Excel sheet.. One of the columns has a description that will be blank or say DELETE. I want to automatically delete the rows that have the DELETE text in that column cell.

Any ideas on how I can loop through each row checking if the cell says delete and if so delete the entire row, then move to the next row?

Thanks in advance for any help.

Regards,

MDA

 

[acron]

If your column has only either text ("Delete&quot or is blank, and the column is say A, the following line of code should work (i.e. delete the lines with an entry in Col A) :

[A:A].SpecialCells(xlCellTypeConstants, 2).EntireRow.Delete

and should be faster than looping through a range. This supposes that there are no formulas, and Blank means empty.

A.C.

 

[MDA]

Hey A.C. --

Thanks for your help. For some reason everything is being deleted??

The range is P1200... The cells are not a formula, they are just text as this point "Delete." The other cells in the column are just blank (empty).

When I execute the code it just deletes all rows, so it seems that it does not see the difference beetween a blank cell and a cell with contents?

Any ideas?

Thanks,

MDA

 

[xlbo]

MDA - try this:
Sub DeleteDeleteandBlank()
lRow = Range("P65536&quot.End(xlUp).Row
Columns("P&quot.Select
Selection.AutoFilter Field:=1, Criteria1:="=", Operator:=xlOr, Criteria2 _
:="=DELETE"
With Range("P2" & lRow)
.SpecialCells(xlCellTypeVisible).Select
.EntireRow.Delete
End With
ActiveSheet.AutoFilterMode = False
End Sub

HTH Rgds
~Geoff~

 

[acron]

Did you replace [A:A] with [P1200] ?

A.C.

 

[xlbo]

Acron - surely if there is text in the other cells ie either Blank or Delete or other text, they are all "constants" and will therefore be deleted ??? Rgds
~Geoff~

 

[MDA]

Thanks for all your help guys... I went with the solution Geoff presented.... I just had to change Criteria1:=&quot;=&quot;, to Criteria1:=&quot;<>&quot;, So that it deletes rows with &quot;delete.&quot; I guess = looks for blank rows.

Thanks again for the help...

As always tek-tips came through for me..

Regards,

MDA

 

[acron]

xlbo,

I understood that the cells were either blank (i.e empty) or had the word 'Delete' in them.

And I did say < delete the lines with an entry >

Blanks do not constitute constants.

A.C

 

[xlbo]

Ooops - misreasd myself - thought it needed blanks and &quot;delete&quot; - must be not blanks then &quot; &quot; or &quot;&quot;
Apologies Acron Rgds
~Geoff~