VBA for shifting blank cells up

[zygissimo]

Hi,

I hope you can help me. I have a very simple task to shift all the blank cells up. I've recorded a macro by Go to -> special -> blanks.
Then delete -> shift cell up. The macro looks like this (see below).

The problem is it does me the job for 300 rows, but my file contains 85.000 rows. And when I select them and execute this macro the workbook remains absolutely empty.

When I try to do Go to -> special -> blanks for 85.000 rows Excel gives me an error "Selection is too large".

many thanks!

Please Login or Register  to view this content.

Moderator Note:

Pls use code tags around your code.

[NeedForExcel]

Here is the probable explanation

This happens in Excel 2007, and earlier versions, because there is a limit of 8192 separate areas that the special cells feature can handle. (This problem has been fixed in Excel 2010.) There are details on Ron de Bruin's website: SpecialCells Limit Problem.

http://blog.contextures.com/archives...-is-too-large/

[zygissimo]

Thank you. I should have mentioned that I am using Excel for Mac. It works on Office for Windows. But it takes a noticeable amount of time.