Delete Row meeting a Criteria

  • Thread starter Thread starter SRS Man
  • Start date Start date
S

SRS Man

Is there a way to delete a row that meets a certain criteria using VBA.

For example, my spreadsheet has 100 rows and I want to delete every row that
has the word "DUP" in it. They will be spreadout over the 100 rows.

Please let me know.

Thanks
 
Try something like the following:

Dim RowNdx As Long
Dim LastRow As Long
LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For RowNdx = LastRow To 1 Step -1
If Cells(RowNdx, "A").Value = "DUP" Then
Rows(RowNdx).Delete
End If
Next RowNdx

This will delete all the rows that have "DUP" in column A.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Thanks Chip


Chip Pearson said:
Try something like the following:

Dim RowNdx As Long
Dim LastRow As Long
LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For RowNdx = LastRow To 1 Step -1
If Cells(RowNdx, "A").Value = "DUP" Then
Rows(RowNdx).Delete
End If
Next RowNdx

This will delete all the rows that have "DUP" in column A.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
Click to expand...
 
Hi Man.

Sounds like "DUP" is the result of a formula looking for duplicates.
If so why not change this to 0 (zero) and sort by that column, and then delete the rows with 0?


Regards Robert
 
Back
Top