Macro to delete rows with "0" values in columns trouble, fast in a workbook slow in anothe

[Bud Wilkinson]

Hello,
I have a seemingly very efficient macro that works lightening fast, (less than 2 seconds), in one workbook and dog slow, (5 + minutes), in another with the very same data.
It is written to look for "0" value cells in several columns then delete entire rows with "0" values in the column then move on to the next column.

I have done the following to troubleshoot.
1. Made sure all named cells / regions are directed to this workbook.
2. Made sure I am calling macro from this workbook and not an unopened one.
3. Insured there were no other outside references I could think of.

Like I said it is super fast in one workbook and super slow in another.

Any Thoughts or suggestions would be appreciated.

Please Login or Register  to view this content.

[bulina2k]

Lots and lots of loops you have there. How about a loop-less method:

Please Login or Register  to view this content.

[Bud Wilkinson]

Lots and lots of loops you have there. How about a loop-less method:

Please Login or Register  to view this content.

Hello bulina2k,
Thanks for the code suggestion. I have been looking for code that would accomplish the goal without so many loops.
I ran your code first on my workbook that always runs my posted code very fast and trouble free before trying it on the workbook that has some code slowing issue.
The data in question is from is from columns A-U which can expand to Z or contract to L potentially. Some rows have zeros in all columns while other rows will have from one to a few zeros in random columns.
It occupies to row 359 to start with, changing as rows with "0" are deleted and could potentially go to row 10,000 or ??? to start with
Row 1 is always a header row and must remain in place.

Unfortunately this code has two negative consequences to start with.
1. it deletes the header row or first row each time it is run even when there are no zeros in that rows data.
2. It doesn't seem to move on to column F or further and zeros remain in those columns in random row cells.

In the code I posted if the value for each column was

Please Login or Register  to view this content.

it would clear the sheet as it saw blank columns to the right of the last populated column as 0.
When I added the quotes around the 0 like

Please Login or Register  to view this content.

it would not wrongly delete rows with blank cells.

It would appear the code doesn't go to the next column as is needed and it also seems to see blank cells in E1:Z1 as 0 and deletes that row. Changed to E2:Z2 it leaves the header row but deletes row 2 even if no zeros are in the data field.

[ikboy]

Try this (may less than 1 seconds)

[bulina2k]

Hi there Bud!
Your initial code was looking for columns E to Z so I made the code accordingly. You are right about the header but I took care of it:

Please Login or Register  to view this content.

Now, if it continues not to move on the next columns please attach a sample dummy file (after you take of the confidential/sensibel data)

[Bud Wilkinson]

Try this (may less than 1 seconds)

ikboy,
Ran this on the workbook that goes very fast with the OP code. This is much simpler and is lightening fast for sure.
Great option with a small amount of code that just works great.
Ran it on my combined workbook that runs the OP code slowly, (like 5 minutes) and this was noticeably faster (like 1.5 to 2 minutes).
Still don't know why the combined workbook runs so much slower.
Thanks for this code help though. It is a much sought after and cleaner way to accomplish my goal in either workbook.
Bud

[bulina2k]

Hi there Bud. Did you tried my modified code? Does it work ?

[Bud Wilkinson]

Hi there Bud!
Your initial code was looking for columns E to Z so I made the code accordingly. You are right about the header but I took care of it:

Please Login or Register  to view this content.

Now, if it continues not to move on the next columns please attach a sample dummy file (after you take of the confidential/sensibel data)

Bulina2k,
Thank You very much for your help. This code is much more what I needed. It did require an error handler as the filter routines often seems to need in VBA when it can no longer find a zero value. Also the screen updating was slowing the process down quite a bit so I modified the code to the following and it runs through every column very fast and does not delete rows with blank cells in columns past the populated U column.
Here is how I modified it to work quickly and without error. it too is stunningly fast on one workbook but takes a long time (like 5 minutes) to run on my combined workbook.

Please Login or Register  to view this content.

[Bud Wilkinson]

So as a side benefit to my OP question, I now have two much cleaner and easier to write ways to accomplish the goal of removing rows with zero values from a large data set thanks to both bulina2k and ikboy.
I am still looking for a reason that the same code works like a rocket in one workbook and like a slug in another. the slow book is only a couple sheets larger and the calcs are very simple. Even when everything is turned off while the VBA runs it really bogs down so I am still searching for that answer so I can't yet call this one solved.
I will take the sensitivity out of my workbooks and post them later for evaluation.
Thanks,
Bud

[bulina2k]

Error? What error? I've made myself a dummy file with columns containing 0's and not containing 0's but the code raised no error for either one of them.
Then... 5 min for 22 columns? Too much! With or without ScreenUpdating, still too much! My guess, something else is happening. Just for the record what is the value of LastRow ?

[Bud Wilkinson]

Error? What error? I've made myself a dummy file with columns containing 0's and not containing 0's but the code raised no error for either one of them.
Then... 5 min for 22 columns? Too much! With or without ScreenUpdating, still too much! My guess, something else is happening. Just for the record what is the value of LastRow ?

bulina2k,
Wow, First let's address the error I got. When your second code post quickly removed all the zeros on my Fast workbook I got a VBA error I have seen before when it could no longer find the criterial value it was looking for in the Rng.AutoFilter Field:=cel.Column - 4, Criteria1:="=0" line and produced this error.
bulina2kErr.jpg
So when I added the error handler lines that stopped.
Secondly, The Screen Updating on my fast workbook only added a couple seconds to the routine and had a lot of flashing.
So when I added the switches to turn off and on the 4 applications (one of them being ScreenUpdating) your code worked flawlessly and very very fast, on that workbook.
The value of LastRow is only 359 so really pretty small amount of data overall. I agree that it takes way too long in my combined or slightly larger workbook.

I am using MS Office Pro Plus 2010

This brings me back to my OP question of the combined workbook being very slow at something. Like I said above I am taking the sensitivity out of my workbooks to post for evaluation, As I do this I am finding that the combined workbook that is slow with any zero row removal code in VBA is also giving some difficulty in manually selecting multiple manually filtered rows and deleting them. It does it but becomes unresponsive and has a transparent white screen. It eventually finishes the manual opperation too but shouldn't take so long.
Still investigating and I will post the workbooks unless I find the anomaly. In which case I will post what that was.
Thanks again

[Bud Wilkinson]

So after working with this for some time now I have a cause for the bog down of the "Remove Zeros" routine. The Delete Rows operation, regardless of using a filter/select/entirerow.delete VBA or manual filter or even with random rows selected manually, right click, delete or any other means of deleting a row in any quantity on any sheet caused my combined workbook to bog down on the Delete Row action.
The combined workbook that gives trouble with the "Remove Zeros" routine and ultimately the Delete Row action is a consolidation of two different workbooks. Book 1 and Book 2
Book 1 took raw data pasted into the "Raw" sheet from an independently generated *.csv file and imported just the desired columns from the "Raw" sheet to its "Refined" sheet. From there the rows with zeros in any column were deleted with the code in my OP or now with bulina2k's or ikboy's code. The "Refined" sheet data was then copied manually from that Book 1 to Book 2 "Refined Data" sheet that did the analysis functions in its "Main" sheet and "Analysis1" and "Analysis2" sheets. Book 2 had its main sheet to handle the bulk of sorting, ranking and display of the data in a table with lots of conditional formatting This Book 1 and Book 2 setup was done before I was charged with the duties. My thought was to combine the two books and perform all the functions within one book called "Combined Book". The workflow would go like this
1. Copy the CSV page data and paste it to the "Raw" sheet.
2. From the Refined sheet run the VBA code to import the desired columns of data from "Raw" based on header name and placement.
3. From the Refined sheet run the VBA code to remove zero cell rows in the data table.
4. Import the Refined data to the Main sheet.
5. Perform the desired analysis from any other sheet on data in the main sheet from routines on "Main", "Analysis1" and "Analysis2".

As soon as the two books were combined by either copying the required 3 sheets of either book to the other, sorting out the links and Modules it suffered while "Remove Zeros" routine was run due to the problem with the Delete Row action as for as I can tell.
deleting one sheet at a time from "Combined Book" and trying the "Remove Zeros" routine missing one of the Book 2 sheets reveled that when the main sheet, the most needed and desired sheet, was deleted the "Remove Zeros" routine was lightening fast again. keep in mind that no data was being calculated or even present in the Main sheet while running the "Remove Zeros" routine. It does however have all of 1000 rows and 21 columns or 21,000 cells containing conditional formatting to color rows of data that match one of three criteria cells. It also seems the original Main sheet was modified several times and wrongly Format Painted more than once for this conditional formatting.

I ended up rebuilding a new Main sheet with only the appropriate conditional formatting in place. As I would set the conditional formatting for the first row then 20, then 50 I would return to the Refined sheet and try the Remove Zeros routine and observe. there was less than a second delay around 20 rows of formatting but as the formatting hit 250 then 500 rows formatting set, it began to slow down the Remove Zeros routine or more appropriately the Delete Rows action. At 1000 rows formatting set on the Main sheet the "Remove Zeros" routine run time on the Refined sheet is around 15 - 16 seconds no matter which code I use to remove zero rows. What is baffling me at this point is that the conditional formatting on a page that doesn't even have the final or any data in it seems to be killing the "Delete Rows" action.

I am wondering if there is any switch like
_Application.ScreenUpdating = False_
that exists to ignore conditional formatting or an entire sheet that can be run with the Remove Zeros routine so it can speed through its work like it should? 15 - 16 seconds isn't too bad with the small amount of data for this example and is way better than 5 plus minutes. I know there is a complicated way to use VB to create a sheet and basically build it the way you want with VB commands if need be but it is both complicated and time consuming. I wanted to maybe use a switch if possible.
Still want to salvage the single book template method with an increase in Delete Rows action speed if I can. If you have any ideas I would love to try them and if I find an easy way I'll post it for you all to see. Otherwise I may try the build a sheet in VB after the data has the zeros removed or stay with the two book method.
Thanks,
Bud

[bulina2k]

[...]
1. Copy the CSV page data and paste it to the "Raw" sheet.
2. From the Refined sheet run the VBA code to import the desired columns of data from "Raw" based on header name and placement.
3. From the Refined sheet run the VBA code to remove zero cell rows in the data table.
[...]

How about importing the data from the Raw into Refined sheet after you filtered out the 0-rows ? And copying only the visible data... Have you tried this?

[Bud Wilkinson]

How about importing the data from the Raw into Refined sheet after you filtered out the 0-rows ? And copying only the visible data... Have you tried this?

Hi bulina2k,
My first method was with two sheets and I wanted it to be done all in one sheet so to filter out the zeros on the *.csv would not really help me out with the overall goal of a consolidated sheet at this point but I'm sure it would speed up that operation. I'm not sure how well a macro works on a *.csv file either but will try it out for a test. I have an example of a workbook that takes *.csv data, imports it to a sheet, re formats it for use in a *.xlsm file and builds several sheets based on the code and an *.xlam Add-In that I can create and add to Excel. I was just trying to do the build more simply for this project.
As it turns out it is the Delete Rows action that is slowed down seemingly by the conditional formatting on the Main sheet. for a typical data selection in my case the Remove Zeros operation is down from 5 plus minutes to 20ish seconds. which is faster than running two separate workbooks and acceptable at this point unless someone can come up with a reason and a fix for conditional formatting on one sheet to make the deletion of entire rows anywhere on the workbook to slow down' It is 1000 rows and 20 columns of conditional formatting that isn't even active when the rows on a different sheet are being deleted.
Still looking for an answer to this characteristic.
Thanks,
Bud

[bulina2k]

Hi Bud,
Please explain these 2 steps
1. Copy the CSV page data and paste it to the "Raw" sheet.
2. From the Refined sheet run the VBA code to import the desired columns of data from "Raw" based on header name and placement.
Is there a code for each/any of them? May we see that code? Specially for the second one?

[jindon]

Assuming header in 1st row.

Please Login or Register  to view this content.

[Bud Wilkinson]

Hi Bud,
Please explain these 2 steps
1. Copy the CSV page data and paste it to the "Raw" sheet.
2. From the Refined sheet run the VBA code to import the desired columns of data from "Raw" based on header name and placement.
Is there a code for each/any of them? May we see that code? Specially for the second one?

Sure,
Sorry if this has been too vague to understand.
Number 1,
It's as simple as it gets. Open the independently generated *.csv file, Click the spot above the 1 and to the left of the A to select the whole page field, Right click A:1 and Copy. In the "Raw" sheet of the combined workbook click in A:1, Right click and Paste.
I know I could write or even copy some code I already have to initiate an explorer file chooser window, open the *.csv, copy all the data and paste it all into my "Raw" sheet then close the *.csv. I may do that next but I haven't gone there yet.
The *.csv file data is already organized in rows and columns with headers. I only need some of the columns for this instance. They need to be reordered in some cases and not in others. Zero value cells under the desired headers, make the entire row they're in useless. There are other column headers that are not needed yet, but may in the future, that often have cells with zero values in the same rows that I need to keep with my chosen header values not being zero.

Number 2,
It is a very simple Function and Sub code combination that allows me to set the header values I want to import, in the order I want them on "Refined" from cells A:1-AZ:1 or many fewer. Regardless of which column they are in on the "Raw" sheet. the code searches the "Raw" sheet in cells A:1-AZ:1 for the same value and if the header value is present, copies that whole column and pastes it below the same header value ion the "Refined" sheet in the order I have set out regardless of where it is on the "Raw" sheet. If the column header value in A:1 on the "Refined" sheet is Apples and that header is found in AZ;1 in the "Raw" sheet it will copy the data from AZ:2 and below on the "Raw" sheet and paste it into A:2 and below on the "Refined" sheet. B:1 "Refined" = Pears, found on Z:1 "Raw", data pastes to B:2 and below on "Refined" and so on. I am usually only concerned with enough columns to populate "Refined" sheet from A to Z or fewer while the *.csv file and "Raw" sheet may have Columns A to AK populated or more so I look to AZ. If a header isn't found on the "Refined" sheet it is overlooked on the "Raw".
Once the "Refined" sheet is populated with the desired data, the Remove Zeros code is run on the "Refined" sheet. This keeps from deleting rows with zeros that are in columns not yet needed that the desired columns have needed data in. Hope I haven't gotten to wordy or lost you by over explaining and that it makes sense.
Here is the column copy and order code. This code runs very fast without any troubles.

Please Login or Register  to view this content.

EDIT:
As I said in my last poste the problem is really in the speed restriction of the Delete Row operation of any sheet when the Main sheet or probable any other sheet is heavy with Conditional Formatting. I have cleared all conditional formatting on the Main sheet and the Remove Zero operation is lightning fast. I have added the conditional formatting back 20 rows at a time and tested the Remove Zeros or Delete Rows operation and the more rows of conditional formatting, the slower Delete Rows goes.

Bud

[Bud Wilkinson]

Assuming header in 1st row.

Please Login or Register  to view this content.

jindon,
Thanks for the input. I'll give it a try as an alternative zero value removal code but I believe I have narrowed down the Delete Rows slowdown to the Main sheet conditional formatting. Removing the existing conditional formatting and cleanly replacing it 20 rows at a time with a retest shows an increase in time required to Delete Rows with more conditional formatting. The conditional formatting even though the table it is to act on is empty seems to cause the Delete Row operation in any other sheet to slow way down. The original conditional formatting was a bit dirty and was modified wrongly more than once.
Bud

[bulina2k]

I've modified a bit your import from Raw code for importing after filtering. Give it a try please.

Please Login or Register  to view this content.

[Bud Wilkinson]

I've modified a bit your import from Raw code for importing after filtering. Give it a try please.

Bogdan,
I got your PM and tried to respond but for some reason it will not allow me to do so.

I did try your code to filter out the zeros during the import from Raw to Refined sheets. It has some promise as well as some undesirable consequences. The promise is worth perhaps trying it in a different way as I will describe below.
My original Sub CopyHeaderColumns() and Function GetHeaderColumn() did a great job of importing only the columns under the matching headers and in the order I laid them out on Refined Row 1 regardless of where they were on the Raw sheet. Also if there were any duplicate headers on the Refined sheet they would be skipped on the second instance leaving a blank column. Both needed characteristics in this instance. The duplicated header columns, say B:1 and C:1 were the same, then in C:x I could add a formula like IF(Bx=X,Y,Z).
Your modifications to Sub CopyHeaderColumns() and Function GetHeaderColumn() had the following effects;
1. It does filter out the zeros in the appropriate manor however.
2. as the filtering is being done on the same workbook as the Main sheet with the conditional formatting, it took the same time as running the remove zeros on unfiltered data on Refined.
3. the importing of the filtered columns to refined would not happen in the order I had the headers laid out on Refined but rather would copy the headers and columns in the same order as found in the Raw sheet and paste over the desired headers and their order laid out on Refined.
4. The instance of a duplicate column as described above was ignored and the next populated column was pasted over the duplicate header and the formulas in that column of Refined.
5. The data on Raw was then modified and missing rows of data that could potentially be needed in another operation.

So remember I said it held some promise though? I am working on a routine to incorporate the

Please Login or Register  to view this content.

command and your filter to import the *.csv Sheet1 data after your modification acts on the data before it is brought into the workbook on Raw.
Once the filtered data is imported to Raw the original Sub CopyHeaderColumns() and Function GetHeaderColumn() under different names can be used to set the order and blank columns without removing any rows and work quickly.
I will need a way to turn off the filter and close the *.csv without saving any changes to preserve it as a data base for future work.

Thanks,
Bud

[bulina2k]

Hi Bud.
Please let me try to answer punctually :

1. It does filter out the zeros in the appropriate manor however.

5. The data on Raw was then modified and missing rows of data that could potentially be needed in another operation.

Remember that it hides not-wanted rows and columns. It doesn't delete them. It doesn't modify them. You just have to unhide them all.

2. as the filtering is being done on the same workbook as the Main sheet with the conditional formatting, it took the same time as running the remove zeros on unfiltered data on Refined.

That is most awkward. I can work it out and resolve the steps 3. and 4. (the order of the columns and the duplicate columns) but if the filtering process takes as much time as removing zeros from Refined, well it doesn't worth it anymore...


As for the .CSV you said that it is copied manually. You could filter (manually or automatically) first and copy the visible range into Raw (also manually or automatically).
Anyway, keep me up to date.
Thanks!

[Bud Wilkinson]

Well I am going to call this case closed even though we have no answer to why any amount of un-populated sheet conditional formatting would slow down either the EntireRow.Delete or Row Filtering and Hiding actions on any other sheet.
Much thanks to bulina2k, AKA Bogdan and jindon for the input and much smaller code that handles deleting rows with zeros in specific columns. Both work very good.
I have settled for now on a bit of code that opens, copies, converts and pastes the *.CSV file from my *.XLSM workbook and if it takes 25 - 45 seconds to properly remove the rows with undesirable zeros then so be it.
I would like to have someone else build a simple workbook with some random data in a Raw sheet, use the Sub CopyHeaderColumns and Function GetHeaderColumn routines into a Refined sheet then in a third sheet add a lot of conditional formatting with no data in it at all. From Column A to Column V worth with three cell fill color conditions with the format painter at say 25 rows at a time up to 1000 total and see if you have the same results in speed change when running the Sub RemoveZeros on the refined sheet. The formatting in my sheet looks like;
CondFormat.jpg
Thanks for all the help and TIA to anyone who tries to load a workbook sheet with conditional formatting to see if they experience the same slowing results.
Bud