Results 1 to 6 of 6
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Macro to Delete Rows containing Certain Data (2007)

    I need a macro that will delete rows containing the following

    1) I need a macro to delete all rows where the words " Totals for Vehicles Stocked in etc appears for eg Totals for Vehicles Stocked in 0804 , Totals for Vehicles Stocked in 0702 etc

    2) In Column L, I want all the rows containing values <45 to be deleted

    Your assistance in this regard will be most appreciated

    Regards

    Howard

  2. #2
    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: Macro to Delete Rows containing Certain Data (2007)

    Instead of a macro, I suggest using AUTOFILTER (Data - Autofilter)

    Add it to the first row.

    Filter the columns to show the phrase ("Contains") and then delete all the rows displayed

    Then filter on COlumn L for >45 and then delete all the displayed rows...

    It is much faster than writing code and you can see what rows you will delete before you delete them...

    Steve

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Re: Macro to Delete Rows containing Certain Data (

    Hi Steve

    Thanks for the advise. Will try Autofilter

    Regards

    Howard

  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: Macro to Delete Rows containing Certain Data (

    If you find you do need a macro, post back as it is not that difficult.

    I find too often people ask for macros when excel has the builtin features to do it manually much faster, especially for a one time task...

    Also if you have a lot of columns to look for "Totals for Vehicles Stocked in " instead of filtering individual ones you could create a temp column and put in row 2 a formula like (adjusting the range as desired)
    =COUNTIF(A2:I2,"*Totals for Vehicles Stocked in*")>0

    Then copy this down the column, and then filter on this column for TRUE to display the rows that contain that phrase, and then delete them...

    Steve

  5. #5
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Re: Macro to Delete Rows containing Certain Data (

    Hi Steve

    Thanks for the help. A Macro would be better as the data is imported every month And I would have to use Autofilter each time to delete the data

    Your assistance in this regard would be most appreciated

    Regards

    Howard

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

    Re: Macro to Delete Rows containing Certain Data (

    Here is such a macro:
    <code>
    Sub RemoveRows()
    Dim r As Long
    Dim m As Long
    Dim n As Long
    ' m is last used row
    m = Cells.Find(What:="*", SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious).Row
    ' n is last used column
    n = Cells.Find(What:="*", SearchOrder:=xlByColumns, _
    SearchDirection:=xlPrevious).Column
    ' Loop backwards through rows
    For r = m To 1 Step -1
    ' Does column L contain a value less than 45?
    If Not Cells(r, 12) = "" And Cells(r, 12) < 45 Then
    Cells(r, 1).EntireRow.Delete
    ' Or does "Totals for Vehicles Stocked in" occur?
    ElseIf Application.WorksheetFunction.CountIf( _
    Range(Cells(r, 1), Cells(r, n)), _
    "*Totals for Vehicles Stocked in*") > 0 Then
    Cells(r, 1).EntireRow.Delete
    End If
    Next r
    End Sub
    </code>
    It operates on the active sheet. If you want to exclude row 1 (probably not necessary, but who knows), change the line
    <code>
    For r = m To 1 Step -1
    </code>
    to
    <code>
    For r = m To 2 Step -1</code>

Posting Permissions

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