Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete Rows (Excel 2000)

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Delete Rows (Excel 2000)

    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.

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Rows (Excel 2000)

    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.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Delete Rows (Excel 2000)

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

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •