Progress indicator not showing when macro is excuted

Hi All,

I have a workbook that data get import through the import function in excel the data get manipulated where rows get clear leaving blank rows the workbook may contain over 20,000 rows. I have create a macro to delete blank rows. The macro work as I expected, but it show no progress indicator when run.

I have done some research on how to show a progress indicator when the macro is run and come across with the following link http://spreadsheetpage.com/index.php...ess_indicator/ .

When the macro is run the progress form is load and execute the Sub DeleteBlankRows in the background and the progress indicator form is showing in the screen is not updating. Can anyone be able to help me with this code. I have attached the workbook that contain the code.DATA_TEST.xlsm


Sorry not sure on how to attached the code in this thread.

Thank You,

Eugene

Hi Eugene,

why don't you just display the % done in the status bar?
On your test sheet, running a code to delete rows takes less than two seconds, so I wouldn't really go through all the effort to show a progress bar.
I realize that's only about 6000 rows of data, but still - upping that to 30000 rows would still make it be done in less than 10 seconds

Try this :

Please Login or Register  to view this content.

Please click the * below if this helps

Hi, eugh,

maybe restrict the loop to only those cells in Column A which are empty (SpecialCells(xlCellTypeBlanks)) and loop through these instead of looping through all cells? Adding a new column with a formula and utilize Autofilter to delete the cells which fit in one go instead of a loop and delete the extra column at the end of the macro?

Ciao,
Holger

Holger made a valid point (which I also thought about, but I took the lazy road)

Try this for speed :

Please Login or Register  to view this content.

Please click the * below if this helps

Hi, JasperD,

if we assume that a blank cell in Column A means that the entire row could be deleted:

Please Login or Register  to view this content.

Ciao,
Holger

Hi HaHoBe,

Thank You for your help, I confused with your code should it be replacing JasperD, Err1:?
Running your code work but it not show any progress indicator the workbook that I'm testing contain more than 80000 rows how can I implement an progress indicator showing progess that the code is running.

Please Login or Register  to view this content.

Thank you again.

Eugh

JasperD,

Thank You again, I'm confused should I replace your previous code all together or just

Please Login or Register  to view this content.

Eugh

Hi, eugh,

if you use the code I posted there is no need for an indicator as long as the areas for empty cells/blanks do not exceed 8192. Maybe test this code on a copy of the worksheet?

Ciao,
Holger

Hi eugh,

itīs not a matter of rows to work on but a matter of matches to find. Each single cell of a blank will count for an area, and the number of areas may not exceed 8192. As I donīt know how many blanks are in you import you may break the code down to segments like

Please Login or Register  to view this content.

The Step number could be increased to a greater number but I choose 10,000 here so the maximum number of loop will be 105 if the worksheet is filled throughout. Due to the deletion of rows there could be a check amended to see if the end of the data set has been reached prior to the end of the loop. 80,000+ rows would mean 9 loops here instead of 80,000+ in your concept.

Ciao,
Holger

Hi, Jasper,

yes for all Versions from 2002 on but not with 2000 and earlier as they should delete the entire range and not only the visible cells. And even using the Autofilter may cause trouble if the areas exceed 8192.

Ciao,
Holger