Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Jul 2001
    Location
    U.S
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete Rows using loop (Excel 97/VBA)

    I've tried to no avail to delete certain rows within a workbook based on specific criteria. I can get it to delete some of the rows but it never deletes all of them. I've tried various methods of looping script that I've gleemed from here, text books, etc. to no avail. Can someone please help. Thanks!! I've attached my code for review.

    Sub DeleteRows

    Dim i as Integer

    For i = 1 To Range ("Impact_Test_Criteria").cells.count
    If Range("Impact_Test_Criteria").cells(i) = 1 Then
    Range("Impact_Test_Criteria").Cells(i).Entirerow.D elete

    Else

    End if

    Next i

    End Sub

  2. #2
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Delete Rows using loop (Excel 97/VBA)

    Eazylee,

    When using a loop counter to delete members of a collection, it works more reliably to delete from the end to the beginning:

    For i = Range("Impact_Test_Criteria").Cells.Count To 1 Step -1 'etc.

    By the way, does the Impact_Test_Critera range contain more than one column? - if so, that also could lead to a counter problem with your code.

    One other thing - you can omit the "Else" statement.

    Gary

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Delete Rows using loop (Excel 97/VBA)

    You need to start at the last row and countdown to row 1. In your current example, if rows 1 and 2 qualify for deletion, then when row 1 is deleted row two becomes row one, but your counter has moved on to 2 so that the original row 2 is passed over and not deleted.

    Try using

    For i = Range ("Impact_Test_Criteria").cells.count to 1 step -1

    and see if that works any better.

    Andrew C

  4. #4
    Star Lounger
    Join Date
    Jul 2001
    Location
    U.S
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Rows using loop (Excel 97/VBA)

    Thank you both!! You must have been typing at the same time. Your suggestion worked like a charm although I'm going to have to take a moment to understand why. [img]/forums/images/smilies/sad.gif[/img] I've been up since last night trying to get this one part of my code to work properly. I didn't think anyone who could help me would be checking the forum on a Sat. but you both definitely blessed me today. Thanks again!!

  5. #5
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Delete Rows using loop (Excel 97/VBA)

    Sorry guys, I've joined this after the horse has bolted etcetera but thought the following may be "helpful" in a devils advocate kinda way:
    set the following code in say "before_right_click" event
    in top of list when right clicked the following code runs pretty smoothly, catching all rows (providing theres no blanks mid range)
    Do
    if activecell.value = "MyValue" then
    activecell.entirerow.delete
    else
    activecell.offset(1,0).select
    end if
    loop until activecell.value = ""

    Alan
    cheshire
    UK

  6. #6
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Delete Rows using loop (Excel 97/VBA)

    Hi Alan,

    It's always interesting to see another way of doing things that you might not have thought of.

    Two reasons why I'd stick with the For...Next method that was originally posted:

    (1) It won't stop if it hits an empty cell
    (2) It's arguably more efficient because it's acting upon a range, rather than a selection.

    Gary

  7. #7
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Delete Rows using loop (Excel 97/VBA)

    Hi Gary,

    There's no argument about it, you are quite right in saying the For ..next is more efficient when running through a range
    The reason I used the do..loop in some of my current "adopted" projects was because up until about a week ago I was ignorant of the fact that I could re-assign the used range of a worksheet using "activesheet.usedrange" and hence for some of the worksheets that were coming my way I had less than 5~10 k rows of data but the used range extended to the 65k row in the worksheet.

    Alan

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Delete Rows using loop (Excel 97/VBA)

    Now that the scrum has collapsed, I may as well pile on. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    -IF- it fits, using AutoFilter in VBA to delete cells is even faster, see JanKarels' trick <!post=here,180759>here<!/post> and my generic version of it <!post=here,246097>here<!/post>.

    And if you are not deleting rows, but performing some other action, the SpecialCells method of narrowing down a range may be even better, as it ignores the non-qualified cells. The SpecialCells Method is the VBA way to do anything available in Goto, Special; it can be one way to further narrow what otherwise would be a very large ActiveSheet.UsedRange.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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