Deleting multiple rows at once

  • I need my Macro to delete multiple rows at once.


    For now I cycle it 1000 times deleting row by row and it works, but takes time to process.


    For y = 1 To 1000
    Rows(x - 1).Delete
    Next


    The problem mainly is that rows have to be designated by x that macro calculates earlier


    Making it cycle 500 times with


    Rows(x - 1).Delete
    Rows(x).Delete


    Doesnt reallly lower processing time as it still works one by one.



    Being able to select multiple rows at once and delete them this way would greatly help me

  • Re: Deleting multiple rows at once


    When deleting, you'll want to count backwards, since the rows keep shifting. This is assuming you're doing something other than a delete, you would do


    Code
    1. For y = 1000 To 1 Step -1
    2. 'Do other stuff
    3. Rows(y).Delete
    4. Next y


    If you truly just need to delete a block of rows

    Code
    1. Range("1:1000").EntireRow.Delete

    Best Regards,
    Luke M
    =======
    "A little knowledge is a dangerous thing."

  • Re: Deleting multiple rows at once


    Luke M
    Problem with rows moving up is solved as you can see I'm using two different variables x and y
    Therefore row never changes, it just deletes one particular row 1000 times.


    I figuerd it out just few minutes ago while looking at somebody else thread about something completly else i noticed this line of code


    Code
    1. Range(Selection, Selection.End(xlDown)).Select


    It solved my problem as I was in need of deleting preferably everything below


    But being able to delete multiple selected rows at once would still be useful so feel free to post ideas.

  • Re: Deleting multiple rows at once


    Glad you figured it out. See the 2nd block of code above for how to delete multiple rows. Or, if you truly want to "select some cells and delete them"...


    Code
    1. Selection.EntireRow.Delete

    Best Regards,
    Luke M
    =======
    "A little knowledge is a dangerous thing."

  • Re: Deleting multiple rows at once


    Yeah but in this case I'm having trouble with designating selection range


    I need to give range that will be determined by variable x (different files different cells that need to be deleted).


    I tried selecting range in one column then going as you say but I don't know how to give this range using variable x


    I tried going for


    Code
    1. Range("A"&x:"A"&x+100).Select
    2. Selection.EntireRow.Delete


    But it simply doesn't work


    huge thanks for input btw, and sorry for inaccurate explanations from me

  • Re: Deleting multiple rows at once


    Try this


    Code
    1. Range("A" & x).Resize(100).EntireRow.Delete


    Resize method lets you state number of rows and/or column to resize to, using original cell(s) are starting point.

    Best Regards,
    Luke M
    =======
    "A little knowledge is a dangerous thing."

  • Newbie here with related question.
    In excel, is it possible to write a macro to remove all rows which for example, which do not contain the symbol @
    Alternatively could there be a macro written which could find and select all rows which contain the symbol @ ?


    If those above are not possible, could a macro be written to delete three rows, skip a row, delete 3 rows, skip one, and on and on?