Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Las Vegas, Nevada, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Macro to delete rows (XP/2003)

    I have a list of customers with names, addresses, etc.

    In column
    Richard Spring

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

    Re: Macro to delete rows (XP/2003)

    If the first row contains column headers (field names), it can be done quite easily without writing a macro:
    - Click anywhere in the table.
    - Select Data | Filter | AutoFilter.
    - Click the dropdown arrow in G1.
    - Select "Out of Business" from the dropdown list.
    - You'll see only the records of customers that are "Out of business".
    - Select all rows from row 2 down.
    - Select Edit | Delete.
    - Select (All Categories) to display all rows again.

    If you prefer a macro:

    Sub DeleteOutOfBusiness()
    Dim r As Long
    Dim n As Long
    n = Range("G65536").End(xlUp).Row
    For r = n To 1 Step -1
    If Range("G" & r) = "Out of Business" Then
    Range("G" & r).EntireRow.Delete
    End If
    Next r
    End Sub

  3. #3
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Las Vegas, Nevada, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Macro to delete rows (XP/2003)

    Forgot about AutoFilter. That works good.
    Also tried the macro and it works as planned.

    It is really a time saver - thank you for your help.
    Richard Spring

  4. #4
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Las Vegas, Nevada, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Macro to delete rows (XP/2003)

    When I saved the above macro I placed it in ThisWorkbook. It worked great on sheet 1,
    but when I tried it on sheet 2 it would not work.
    I copied the macro and placed it in sheet 2 folder, renaming it DeleteOutOfBusiness1().
    Still does not work. Must be a "senior" moment day - can not get any thing to work.

    Any suggestions?
    Richard Spring

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

    Re: Macro to delete rows (XP/2003)

    The macro should not be in ThisWorkbook, nor in any of the worksheet modules, but in a standard module. Select Insert | Module in the Visual Basic Editor to create such a module, then paste the macro into it.

    The macro should act on whatever the active worksheet is when you run it.

  6. #6
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Las Vegas, Nevada, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Macro to delete rows (XP/2003)

    Thank you.
    Perhaps tomorrow will be a better day.
    Richard Spring

Posting Permissions

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