Deleting multiple rows at once in VBA

Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 24

Thread: Deleting multiple rows at once in VBA

  1. #1
    Board Regular
    Join Date
    Jul 2009
    Posts
    191
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Deleting multiple rows at once in VBA

     
    Hi,

    I need to make a macro that will delete a row when a condition is met. The problem is, I got more or less 2000 rows per worksheet to delete and it's very time consuming to delete them one by one.

    So, is there a way to "store in a variable" all the rows I need to delete and then delete them all at once (like using HOLD Control + click rows and then delete) ?

    Thanks!

    Samfolds

  2. #2
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    8,495
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Deleting multiple rows at once in VBA

    How about assign a flag for rows you want to keep, filter out those rows and then use specialcells(xlvisible).rows.delete?

    You may need to check the syntax for above but that I think would be quickest way.


  3. #3
    MrExcel MVP
    Join Date
    May 2009
    Posts
    14,759
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Deleting multiple rows at once in VBA

    Dim deleteRng as Range

    Then set deleteRng = the first row you want to delete. Subsequent rows can be added by using:
    deleteRng = Application.Union(deleteRng,Range("NextRowYouWantToDelete"))

    continue to add all rows to be deleted then

    deleteRng.Delete will remove them all in one shot

  4. #4
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    8,495
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    0 Thread(s)

    Default Deleting multiple rows at once in VBA

    Hi JoeMo,

    Can you tell me why this is failing on the IF statement? I have numbers 1 to 31 running down column A and I'm trying to use your solution to Samfolds problem:
    Code:
    Sub sort_match()
    Dim delRange As Range
    Dim i As Long
    i = Range("A" & Rows.Count).End(xlUp).Row
    Do
    If WorksheetFunction.Mod(Range("A" & i).Value, 2) = 1 Then
        delRange = Application.Union(delRange, Range("A" & i).EntireRow)
    End If
    i = i - 1
    Loop Until i = 1
    MsgBox "Range ready for deletion"
    delRange.Delete
    End Sub


  5. #5
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    8,495
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Deleting multiple rows at once in VBA

    Using Excel2007 - in VBA, it doesn't seem to recognise .Mod after WorksheetFunction hence failing...


  6. #6
    Board Regular
    Join Date
    Jun 2008
    Location
    Cleveland, OH
    Posts
    343
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Deleting multiple rows at once in VBA

    I still use 03; but i think the following syntax would work...

    Code:
     
     If Range("A" & i).Value Mod 2 = 1 Then

  7. #7
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    8,495
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Deleting multiple rows at once in VBA

    Thanks - also realised I need to define my first instance of delRange (e.g. Set delRange = )


  8. #8
    Board Regular
    Join Date
    Jun 2008
    Location
    Cleveland, OH
    Posts
    343
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Deleting multiple rows at once in VBA

    You would also need to set delrange before starting the loop; something like

    Code:
    Set delRange = Range("A" & ActiveSheet.Rows.Count)
    Then, another change;

    Code:
    delRange = Application.Union(delRange, Range("A" & i).EntireRow)
    To...

    Code:
        Set delRange = Union(delRange, Range("A" & i))
    Then

    Code:
    delRange.Delete
    To...

    Code:
    delRange.EntireRow.Delete

    ***was a little slow to post; looks like you've got it now.

  9. #9
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    8,495
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    0 Thread(s)

    Default Deleting multiple rows at once in VBA

    So now I have this which is still not working:
    Code:
    Sub sort_match()
    Dim delRange As Range
    Dim i As Long
    i = Range("A" & Rows.Count).End(xlUp).Row
    Set delRange = Range("A" & i)
    Do
        If Range("A" & i) Mod 2 = 1 Then
             SET delRange = Application.Union(delRange, Range("A" & i))
        End If
        i = i - 1
    Loop Until i = 1
    delRange.EntireRow.Delete
    End Sub
    EDIT: missed SET in red above out which is why it didn't work. Working now, thank you


  10. #10
    Board Regular jbeaucaire's Avatar
    Join Date
    May 2002
    Location
    Bakersfield, CA
    Posts
    6,007
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Deleting multiple rows at once in VBA

      
    Jack, I use this technique quite a lot for my deletion routines, this line of code you have seems to include a row of the actual original data in the delete range:
    Code:
    Set delRange = Range("A" & i)
    I typically do the same LastRow thing, but then I seed the delRange with a cell outside the data range, most commonly for me:
    Code:
    Dim LR as Long
    LR = Range("A" & Rows.Count).End(xlUp).Row
    
    Set delRange = Range("A" & Rw + 10)
    Microsoft MVP 2010 - Excel
    Jerry Beaucaire's Excel Tools

    "Actually I *am* a rocket scientist." -- JB

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com