Trouble running delete rows macro for large excel file

[MSDemocrat]

I have an excel file converted from a wordperfect file that contains roughly 600,000 lines of data. The data is currently in its raw form in excel ( see attached example). I am trying to run the following macro to delete empty rows and it will not finish. I have let it run for 8+minutes and it sends excel into "not responding" mode. The macro I am using is:

Please Login or Register  to view this content.

I have run this macro a smaller test batch of data and it ran pretty quick. Does anyone have advice on how to fix this? Is running macros on 500,000+ rows even possible? After this I have several other macros to run to format the data the way it needs to be.

[Leith Ross]

Hello MSDemocrat,

This macro deletes blank rows only. Is that what you want to do?

[MSDemocrat]

Yes. I thought I was starting with the easy stuff. Then was going to move onto some of the other formatting macros. I am going to end up deleting some other lines but started with empty rows just to see how macros woudl run on such a large file.

[Leith Ross]

Hello MSDemocrat,

Generally, blank rows are removed to consolidate data into a contiguous range. The problem with the code may be because it is using UsedRange to determine the end of the data. Which column will always have data in it?

[Mumps1]

Assuming that if the row is blank then no column will have data, will this work?

Please Login or Register  to view this content.

[Leith Ross]

Mumps,

There may be problem using this method with 600,000 rows of data. SpeciallCells has a maximum limit of 8,191 Range Areas. A Range Area is any non contiguous cells within the range limits.

[MSDemocrat]

Yea, I tried running Mumps' macro and excel quit responding after 5 minutes(I let it run for 10). Would it be easier to just run macros for smaller sections like A1:A100000 or do i need to split data up in wordperfect before i bring it into excel?

[MSDemocrat]

Hello MSDemocrat,

Generally, blank rows are removed to consolidate data into a contiguous range. The problem with the code may be because it is using UsedRange to determine the end of the data. Which column will always have data in it?

Column A will always have data in it. I was trying to remove the blank rows and get data into a contiguous range to reduce the number of rows for future macros. The end goal is to only keep rows with FEDERAL AID (GRANT) Number, CONTRACT NUMBER, VENDOR, ACTIVITY, and ACTIVITY TOTAL. I will then reorganize this data into a Vertical Table and have excel lookup data based on contract number and add to the appropriate line.

[Leith Ross]

Hello MSDemocrat,

Sorry the reply took so long. I have been experiencing problems connecting with the forum. Have you had any problems?

Try this revised macro. It will let you interrupt it if you need to.

Please Login or Register  to view this content.

[MSDemocrat]

Hello MSDemocrat,

Sorry the reply took so long. I have been experiencing problems connecting with the forum. Have you had any problems?

Try this revised macro. It will let you interrupt it if you need to.

Please Login or Register  to view this content.

Leith, I was having problems with the forums yesterday afterrnoon as well. I ran into the same issue with this code as with my original code and the other ones supplied in this thread. Excel just gets bogged down and will not respond. This latest code did allow me to exit the macro without having to force close excel and reopen so that was nice I tested your code on a previous version of my project and it works great with 70,000-80,000 lines and removing about 1100 empty rows.

However, i did find a work around. Instead of deleting emptry rows, I filtered out the empty rows and copy pasted to a new worksheet and that worked well.

I thank you and Mumps for your assistance and i will be adding this macro to my arsenal for future use.

[Mumps1]

Hi Leith. I was also having connection problems. Thank you so much for the information about the SpeciallCells method. I will add it to my knowledge base (still learning!!!).
MSDemocrat, you are very welcome.