+ Reply to Thread
Results 1 to 10 of 10

Deleting rows in more faster way

  1. #1
    Forum Contributor
    Join Date
    09-05-2012
    Location
    It's more fun in the Philippines :D
    MS-Off Ver
    Excel 2007
    Posts
    209

    Deleting rows in more faster way

    Hi experts,

    I would like to ask if there is more faster procedure than my code below?

    Please Login or Register  to view this content.
    The workbook have 15000 rows and the record on the sample code most likely have results of 3000 rows.

    Is it more faster if i will filter first those records (within the date range) then delete those not needed than
    loop through all rows to compare the dates.

    (I asked this question because I'm currently running this module and I'm not done yet. :D
    I'll try this after but hoping I can get quick answer here.)

    Thanks in advance.
    Last edited by thisisgerald; 10-03-2012 at 03:29 AM. Reason: Edited (wrong use of tags)

  2. #2
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2013 on Win10 (desktop), 2007 on Win10 (notebook)
    Posts
    7,998

    Re: Deleting rows in more faster way

    Hi, thisisgerald,

    turning off ScreenUpdating and turning Calculation to manual may speed up your macro.

    Have you thought about using the Autofilter to narrow down the lines to be deleted?

    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  3. #3
    Forum Contributor stojko89's Avatar
    Join Date
    05-18-2009
    Location
    Maribor, Slovenia
    MS-Off Ver
    MS Office 365
    Posts
    913

    Re: Deleting rows in more faster way

    Try entering at the top of the code
    Please Login or Register  to view this content.
    And at the end of the code

    Please Login or Register  to view this content.

  4. #4
    Forum Contributor
    Join Date
    09-05-2012
    Location
    It's more fun in the Philippines :D
    MS-Off Ver
    Excel 2007
    Posts
    209

    Re: Deleting rows in more faster way

    Hello Holder,

    I've set the ScreenUpdating to false and also the Calculation to manual. I think this only delete 10 rows / second.

    I'll give a try on the AutoFilter later. Do you have any example on using that?

    Thanks,
    Gerald

  5. #5
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2013 on Win10 (desktop), 2007 on Win10 (notebook)
    Posts
    7,998

    Re: Deleting rows in more faster way

    Hi, Gerald,

    sadly not on hand (most of the examples are on a different computer I canīt get to right now). If you work with dates on the Autofilter you should convert the Dates to Longs before passing them as criteria. And having made a choice you could either delete all chosen lines or work through them using a Column and SpecialCells(xlCellTypeVisible).

    There arenīt no events included behind the sheet because then you could use Application.EnableEvents = False at the start and Application.EnableEvents = True at the end to stop the event handler to take action.

    If you need help on your actual file Iīd ask you to attach a small sample where the action can be fulfilled.

    Ciao,
    Holger

  6. #6
    Forum Contributor
    Join Date
    09-05-2012
    Location
    It's more fun in the Philippines :D
    MS-Off Ver
    Excel 2007
    Posts
    209

    Re: Deleting rows in more faster way

    Hello Holger,

    Attached file is list of dates only.
    I want the dates from June 4 to June 8 to be left on the same place(Column A of Sheet 1)

    Thanks!
    Attached Files Attached Files

  7. #7
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2013 on Win10 (desktop), 2007 on Win10 (notebook)
    Posts
    7,998

    Re: Deleting rows in more faster way

    Hi, Gerald,

    please try this code on a copy of the sheet or in the sample:
    Please Login or Register  to view this content.
    Right now the header may be removed as no check is done to get the number of rows provided by the Autofilter.

    Ciao,
    Holger

  8. #8
    Forum Contributor
    Join Date
    09-05-2012
    Location
    It's more fun in the Philippines :D
    MS-Off Ver
    Excel 2007
    Posts
    209

    Re: Deleting rows in more faster way

    Quote Originally Posted by HaHoBe View Post
    Hi, Gerald,

    please try this code on a copy of the sheet or in the sample:
    Please Login or Register  to view this content.
    Right now the header may be removed as no check is done to get the number of rows provided by the Autofilter.

    Ciao,
    Holger

    Hello Holger,

    I'm getting a run-time error "Type mismatch" on this line

    Please Login or Register  to view this content.
    If I change the "." to "/" nothings happening.

    Regards,
    Gerald

  9. #9
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2013 on Win10 (desktop), 2007 on Win10 (notebook)
    Posts
    7,998

    Re: Deleting rows in more faster way

    Hi, Gerald,

    the error you encountered is most certainly due to the different ways a date is set up on my computers (Day Month Year separated by .) to yours. Please try the altered code (AFAIR DateSerial should return the value in the way more suitable for international purposes and programming in VBA):

    Please Login or Register  to view this content.
    Ciao,
    Holger

  10. #10
    Forum Contributor
    Join Date
    09-05-2012
    Location
    It's more fun in the Philippines :D
    MS-Off Ver
    Excel 2007
    Posts
    209

    Re: Deleting rows in more faster way

    Thanks Holger!

    This one works more faster than my code above.

    Thumbs up! (Rep. added)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1