Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Sep 2003
    Location
    Camp Springs, Kentucky, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete row based on cell value (Excel 2002/SP2)

    Does anyone know of an easy way to delete multiple rows of a spreadsheet based on the value of cell in a given column using VBA?

    For example, the cells in one column contains a value of "Y", "N" or null. I want to delete all of the rows that have a "Y" in this column.

    I've tried sorting by this column, autofiltering for "Y" and then deleting all rows from 4 to 65536, but there are some rows below this standard set of data that contain other data (a blank row separates the sections). These addition rows end up getting deleted also.
    Another catch is that sometimes there is only one row that contains a "Y", sometimes there are no rows and sometimes there are many rows.

    Thanks,
    Joe

  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: Delete row based on cell value (Excel 2002/SP2)

    Check out the code in <post#=277602>post 277602</post#>. It tests for blank cells (= "") but it could be modified to compare other values.

    FYI, It is important in deleting rows to start at the bottom and move upwards, so your "loop" does not get "corrupt".

    Steve

  3. #3
    Lounger
    Join Date
    Sep 2003
    Location
    Camp Springs, Kentucky, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete row based on cell value (Excel 2002/SP2)

    Thanks again. Works like a charm. [img]/forums/images/smilies/smile.gif[/img]

  4. #4
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    England
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete row based on cell value (Excel 2002/SP2)

    try this for size
    Sheets("com041").Select
    77 Selection.autofilter Field:=13, Criteria1:="s"
    78 Range("J4").Select
    79 Range("A4:N6000").sort Key1:=Range("J4"), Order1:=xlAscending, Header:= _
    xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    80 Application.ScreenUpdating = False
    81 Application.Calculation = xlCalculationManual
    82 lastrow = ActiveSheet.UsedRange.Rows.Count
    83 For r = lastrow To 1 Step -1
    84 If UCase(Cells(r, 13).Value) = "S" Then Rows®.delete
    85 If LCase(Cells(r, 13).Value) = "s" Then Rows®.delete
    86 Next r
    87 Application.ScreenUpdating = True
    88 Selection.autofilter

    substituting your values for mine

Posting Permissions

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