Delete Rows (Excel 2000)

Topic

New Reply

My data list has 12,000 rows. I want to delete those rows that do not have the word “Test” in Column F”. What is the code I would write to delete the rows that do not match? Thank you.

Reply | Quote

Replies

You can do this without code:
– Activate Data | Filter | AutoFilter.
– Click the dropdown arrow in column F.
– Select (Custom…)
– Specify contains Test.
– Click OK.
– Select the filtered rows.
– Select Edit | GoTo…
– Click Special…, then Visible cells only, and click OK.
– Select Edit | Delete.
– Turn off the AutoFilter.

In code:

Dim lngRow As Long
Dim lngMaxRow As Long
lngMaxRow = Range(“F65536”).End(xlUp).Row
For lngRow = lngMaxRow to 1 Step -1
If InStr(Cells(lngRow, 6).Value, “Test”) > 0 Then
Rows(lngRow).Delete
End If
Next lngRow

The code loops backwards on purpose. Looping forward would cause confusion, since the range to be checked shrinks each time a row is deleted.

Reply | Quote

Once again you have educated me. I never knew Excel could delete the rows I did not want! Once I eliminate the rows I don’t need I then sort them by Columns G, F, and J. Every time there is a change in Column G then I need to take that group and paste the rows on a new sheet. Here is my issue: 1)The list changes every month. The columns stay the same, but the rows increase or decrease. 2) After I eliminate the rows I do not need, I will sort and then I need to copy and paste each group on a new sheet. Please help and thank you.

Reply | Quote

Can the code in response to Covert to Multiple Spreadsheets (Excel 2003) be modified to do what you need?

Steve

Reply | Quote