Page 1 of 3 123 LastLast
Results 1 to 10 of 24

Deleting multiple rows at once in VBA

This is a discussion on Deleting multiple rows at once in VBA within the Excel Questions forums, part of the Question Forums category; Hi, I need to make a macro that will delete a row when a condition is met. The problem is, ...

  1. #1
    Board Regular
    Join Date
    Jul 2009
    Posts
    191

    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
    4,949

    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
    8,172

    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
    4,949

    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
    4,949

    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

    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
    4,949

    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

    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
    4,949

    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
    5,774

    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

Page 1 of 3 123 LastLast

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