how to remove entire rows if any cell contains numbers over 1500

  • hi,


    i have a spread sheet which gets generated every week, so its not a workbook I keep.


    Every week I sort the 6 row spreadsheet by number and then delete everything with cell C over 1500. There are numbers from 1 to infinity in this cell.


    Is there a way to simply remove all rows with numbers over 1500? Remembering it would be something I eitehr have to configure to the generated workbook every day its generated, or operated by my own desktop workbook linking into this one?


    Any help would be wonderful



    Thankyou
    Isabella

  • Re: how to remove entire rows if any cell contains numbers over 1500


    In its most direct form, you can use a loop statement to do this:



    To use it for any workbook, but in in your personal workbook in the VBE. I set the loop to end at cell 150000, but you can change that to whatever number you want.

    Regards,


    WidgetWonka
    Puuuureeee Imagination

  • Re: how to remove entire rows if any cell contains numbers over 1500


    Hi Isabella


    give this a go.



    for advice on how to use macros go here:
    http://www.contextures.com/xlvba01.html#Regular

  • Re: how to remove entire rows if any cell contains numbers over 1500


    Hi Guys,
    So both these VB snippets I simply F11 and enter it in every week?
    How do I then activate it?
    thanks soooooooooo much
    Isabella

  • Re: how to remove entire rows if any cell contains numbers over 1500


    No loop required

    Code
    Sub test()
    With Range("c1", Range("c" & Rows.Count).End(xlUp))
        .AutoFilter 1, ">1500"
        On Error Resume Next
        .Resize(.Rows.Count - 1).Offset(1).SpecialCells(12) _
        .EntireRow.Delete
        On Error GoTo 0
        .AutoFilter
    End With
    End Sub
  • Re: how to remove entire rows if any cell contains numbers over 1500




    Thankyou so much jindon, trunten and wigetwonka!


    You mention
    "You could either enter it every week or have a spreadsheet with that macro in it and make sure you have the sheet you wish to delete rows from active before you run the macro. "


    Is there a way to have a macro in excel so that anytime I have excel open I can run that macro aka its not workbook dependent and can be run on any workbook, so when i have that worksheet open I can simply hit it to remove unwanted data? Or is that a ridiculous fantasy?


    Isabella :)

  • Re: how to remove entire rows if any cell contains numbers over 1500


    Quote from jindon;584038

    No loop required

    Code
    Sub test()
    With Range("c1", Range("c" & Rows.Count).End(xlUp))
        .AutoFilter 1, ">1500"
        On Error Resume Next
        .Resize(.Rows.Count - 1).Offset(1).SpecialCells(12) _
        .EntireRow.Delete
        On Error GoTo 0
        .AutoFilter
    End With
    End Sub


    nice :smile:


    (ctrl-c, ctrl-v)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!