Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Mar 2003
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete Rows in Excel (VBA) (Excel 2000)

    I am trying to delete rows in a range were the cell value has "US", see code below. This works fine but has to be run several times to delete all the rows were the cell has "US" in it. I understand why this happens as the row is deleted and the subsequent rows shift up and hence rows are "skiped". I need to return to the previous row before continuing? How do I do this?

    For Each rngRow In rngData
    If rngRow.Cells(1, 11).value = "US" Then
    rngRow.Delete
    End If
    Next rngRow

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Rows in Excel (VBA) (Excel 2000)

    You have to work your way up instead of down:

    Sub DelRows()
    Dim lCount As Long
    Dim lLastRow As Long
    lLastRow = ActiveSheet.UsedRange.Rows.Count
    For lCount = lLastRow To 1 Step -1
    If ActiveSheet.Cells(lCount, 1) = "us" Then
    ActiveSheet.Cells(lCount, 1).EntireRow.Delete
    End If
    Next
    End Sub

    This assumes a cell on row 1 has been used (if not, the usedrange.rows.count misses some rows)...
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Rows in Excel (VBA) (Excel 2000)

    Something like this should do what you want:

    <pre>Dim rngData As Range
    Dim lLastRow As Long, I As Long
    lLastRow = rngData.Rows.Count
    For I = lLastRow To 0 Step -1
    If UCase(rngData.Cells(I, 11).Value) = "US" Then
    rngData.Cells(I, 11).EntireRow.Delete
    End If
    Next I
    </pre>

    Legare Coleman

  4. #4
    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 in Excel (VBA) (Excel 2000)

    Jan Karel <!profile=pieterse>pieterse<!/profile> also has an neat alternative way to delete rows based on criteria using AutoFilter, demonstrated in <post#=180759>post 180759</post#>.
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Rows in Excel (VBA) (Excel 2000)

    Hi John,

    I forgot about that one! Thanks.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    Lounger
    Join Date
    Mar 2003
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Rows in Excel (VBA) (Excel 2000)

    Thanks that works brilliantly <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> - I have been playing around with this for days and it has been so frustrating and I new there would be a simple solution. This forum is excellent.

  7. #7
    Lounger
    Join Date
    Mar 2003
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Rows in Excel (VBA) (Excel 2000)

    Thanks for this, I have solution to this from Legare, but was trying yours from practice but it does not work for me. I do not have the UsedRange property, is this specific to XP?

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Rows in Excel (VBA) (Excel 2000)

    The UsedRange property is there as from XL97, maybe even XL5. You should have it. I've seen cases where the intellisense doesn't work very good. I never get the methods and properties when I use e.g. Activesheet, but they *are* there.

    Do you get a compile error?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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