Excel deletion limitations

[s267435]

I have the following code to delete blank lines in my sheet.

Please Login or Register  to view this content.

unfortunately I have over 44,000 rows to go through.
I read that xlCellTypeBlanks has an 8,000 plus limitation

I have tried a loop but it takes approx. 3 minutes to run through and delete the empty/blank cells.

I have searched numerous sites for ideas but with little success.

Does any one have a better idea/method to accomplish this task ???

Thanks for the help.

[stanleydgromjr]

s267435,

Welcome to the Excel Forum.

Sorry, wrong post.

[shg]

Try this:

Please Login or Register  to view this content.

[DonkeyOte]

unfortunately I have over 44,000 rows to go through.
I read that xlCellTypeBlanks has an 8,000 plus limitation

Just to add - yes, SpecialCells has a limitation on the number of non-contiguous ranges it can process.

General advice would be to sort the data by column containing Blanks so as to generate a contiguous range and then apply SpecialCells before reverting sort order based on prior conditions.

this approach and others are covered at: http://www.rondebruin.nl/specialcells.htm

[Leith Ross]

Hello s267435,

Here is an optimizing macro. This will limit the size of the area to only the rows and columns with data. Only the data is shifted after the blanks are deleted. The cell formatting remains the same. The trials on this macro averaged about 2.8 secs on my machine to shift 12000 rows by 5 columns wide. Your results will be different, but should be quite fast.

Please Login or Register  to view this content.

[s267435]

Thank all of you for the responses.

Shg, I attempted to run your code in my sheet but it is not faster than the current loop I have.

Leith, I also attempted to run your code but received -
Error 1004 at this line :
Set Rng = wks.Range("C1", wks.Cells(Rows.Count, "C").End(xlUp))
- not sure how you executed it ????

[Leith Ross]

Hello s267435,

I must have posted the uncorrected version of the code. Well, you're in luck because I made some other changes to improve the code. his version is also a little bit faster than before.

Please Login or Register  to view this content.

[s267435]

Leith, thanks for the effort your are putting into this thread.

However, I still receive an Error 1004 at line:

Set RngEnd = Wks.Cells(Rows.Count, Rng.Column).End(xlUp)

currently debugging but other suggestions are welcome.

Thanks

[Leith Ross]

Hello s267435,

Since I know this code works, something else is wrong. Please attach the workbook so I review it.