+ Reply to Thread
Results 1 to 5 of 5

Very Large Data Sets - Deleting Rows

  1. #1
    Registered User
    Join Date
    03-10-2014
    Location
    Texas
    MS-Off Ver
    Excel 2013
    Posts
    4

    Very Large Data Sets - Deleting Rows

    Hey Group,

    It has been a while. Hope this is an easy question.

    I am now working with very large datasets of almost 50,000 rows and 40+ columns of data per worksheet with a dozen worksheets in the workbook. The data is weather information taken at every minute of the day. Each worksheet is a month of data. I am trying analyze the data but that is taking a tremendous amount of time. Therefore, I am trying to reduce the minute information to an average hourly information.

    With some help, I am using the code below (which is much better than the code I had been using for a long time).

    In column A I have created an identifier to provide an * if the data is not required and blank if it is. Then I run the code to find those rows and delete them. However, with almost 50000 rows of data, it is very time consuming. The end result is around 800 rows.

    First, is there a better way to work with so much data without deleting portions of the whole data?
    Second, the code below works but takes a long amount of time. Is there a more efficient way?

    Please Login or Register  to view this content.

  2. #2
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Brooklyn, NY
    MS-Off Ver
    Office 365
    Posts
    1,172

    Re: Very Large Data Sets - Deleting Rows

    Add the below code to a module:
    Please Login or Register  to view this content.
    ...then call OptimizeCode_Begin in the beginning of your code, and at the end of your code call OptimizeCode_End

    Hope this does something

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    45,003

    Re: Very Large Data Sets - Deleting Rows

    If the identifier in column A is based on a formula, why not just use Autofilter based on the conditions in that formula? Or just filter on column A and delete visible rows.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    1,021

    Re: Very Large Data Sets - Deleting Rows

    I agree with Trevor, it can be done without using VBA.
    But if you need vba, this one might do the job.
    Try this:
    The output is in sheet2
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    03-10-2014
    Location
    Texas
    MS-Off Ver
    Excel 2013
    Posts
    4

    Re: Very Large Data Sets - Deleting Rows

    This was helpful. Thank you

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Deleting rows in a large data sample
    By Poetae in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 2
    Last Post: 04-01-2015, 02:44 AM
  2. Poisson with large data sets - HELP!!
    By stufferonald in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 12-19-2013, 03:24 PM
  3. Combining large sets of data
    By cab0317 in forum Excel General
    Replies: 9
    Last Post: 10-02-2013, 12:50 PM
  4. Working with large sets of data
    By Bishonen in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-11-2013, 06:19 AM
  5. Deleting large numbers of rows
    By dchun in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-04-2013, 03:53 PM
  6. [SOLVED] VLOOKUP in large Data sets of more than 16384 rows
    By Bluewolf in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 04-03-2006, 04:45 PM

Tags for this Thread

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