Add extra strings to search for in routine.

[webwyzard]

Hi I am trying to allow the use of more than one search string in a sub that deletes rows containing certain text.

I modified the some code as below to remove a stored text string or allow entry of something different.

How could i add say four extra text strings to search for?

Thanks

Neil

Please Login or Register  to view this content.

[Bernie Deitrick]

Enter the substrings separated by commas when prompted.

Please Login or Register  to view this content.

[webwyzard]

Hi Bernie

One weird problem. If i use my code just to remove lines containing "cancelled" it takes 3 seconds. With your code mod it takes over a minute? I can not for the life of me see why unless the "For i = LBound(v) To UBound(v)" takes up processor time?

Any ideas? i could upload the htm file if you have time to take a look.

Thanks

Neil

[webwyzard]

I have uploaded the test data file i am working with if it helps.

I have noticed that i could possibly work the solution another way.

The rows i need to keep all contain three sets of zeros in the columns (Commission Taxes Swap) immediately before the profit column.

So i could search each row and if it finds a 00 in swap column move down a row, and if not delete it.

Regards

Neil

[Bernie Deitrick]

The reason that the code is slow for your workbook is that every time that a row or group of rows is deleted Excel will rebuild its calculation tree. Speeding up the code is simple - flag the rows to delete using a column of values, then sort the entire sheet based on the column of flags, and delete the rows in one go.

Please Login or Register  to view this content.

[webwyzard]

Bernie

Thanks for that i will give it a go.

I still do not understand why my code does not cause excel to rebuilt it's calculation tree but your simple mod did.

It would be nice to understand the way this works.

Thanks

Neil

[Bernie Deitrick]

In your testing, did you use the same starting file, and look for the same string using both techniques? If you deleted fewer rows with one, and more with the other, then that could explain the difference.

[webwyzard]

Bernie

Strangely i posted a reply to you a few days back but i see it does not appear?

Anyway yes i did use exactly the same file and just discarded and opened again for each test. So yes the mod to the code caused it.

Would like to understand why as it makes it unuseable.

Regards

Neil

[Bernie Deitrick]

How many rows are being deleted using the looping version compared to the non-looping version? Which macro did you use? What do you mean by unusable? Does Excel crash, or is the file corrupted, of does it just take a long time? Can you share your test tile?

[webwyzard]

Bernie

The same number of rows is being deleted. The file is attached to an earlier reply for viewing (4th post). In the test i was just deleting rows with the word "cancelled" in them.

Unuseable was perhaps the wrong term but it takes way too long. The earlier piece of vba in first posting was being compared to your version with the looping ability.

Regards

Neil

[Bernie Deitrick]

Your attached zip file contained an htm file, not a .xlsx or .xlsm, and was unviewable on my system.

[webwyzard]

I did indeed and Excel will open it fine. Just file open and choose htm type and it opens up normally.

[Bernie Deitrick]

Sorry - I should have been more specific: opening the HTM file in Excel shows a blank sheet, with the message "Missing file:....." I think the htm file is linked to a file on your local computer, and so opens fine when you try.

[webwyzard]

Ah

No idea how i managed that. I will upload from work tomorrow

Zip should be there now as a .xlsx the .htm would not save in the zip no matter what I did!

[Bernie Deitrick]

Sorry - I did not get a notice that you had changed your post: notices only arrive for new replies.

My code took 0.617 seconds to process your file for "cancelled", so I am not sure what your problem is.

Are you running the code with only that workbook open? With that sheet as the activesheet? Do you have data connections? A lot of other formulas that reference this sheet? Other events? What happens if you run the code using this macro to call it? (Put the code into the same codemodule and run SpeedMacro

Please Login or Register  to view this content.

[webwyzard]

Bernie.

Speedmacro running original code 1 Second

Speedmacro running your modified code 5 seconds.

The htm test file was perhaps slightly smaller than original but it still shows a slowdown. It is however much quicker than it was working last time? No idea why.

I only had the one worksheet open and no other load that i could see. I will try with a much larger file later and see what happens.

BTW Your code that flags the lines to delete is lightning fast (but i suppose you knew that already)!

I did notice that i could shorten the work done by deleting all rows that did not have a certain column with 0.00 in. Rather than selecting multiple values to delete by. In fact there seems to always be three consecutive columns with 0.00 in the ones to keep.

Is there a simple way to alter your code to delete all rows that do not have these values?

thank you so much for your help with this.

Regards

Neil

[Bernie Deitrick]

I think your usedrange may be larger than the range that you are actually using - that happens with older workbooks that have had a lot done to them - so try changing

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[Bernie Deitrick]

As to this:

I did notice that i could shorten the work done by deleting all rows that did not have a certain column with 0.00 in. Rather than selecting multiple values to delete by. In fact there seems to always be three consecutive columns with 0.00 in the ones to keep.


You could change the code to this - change

"G" & lngR & ":I" & lngR

to reflect the three consecutive columns - as written, they are G, H and I

Please Login or Register  to view this content.

[webwyzard]

Hi Bernie

The revised code to check the usedrange works in less than a second

Your code modified to keep rows with 0.00 in works when i changed the <> to = in < If Application.CountIf(WS.Range("G" & lngR & ":I" & lngR), 0) <> 3 Then >

However the line WS.Cells(WS.Rows.Count, "A").End(xlUp).Row which counts the rows for WS.Range("A2").Resize(lngC - 1).Value = "Remove" extends the selection down beyond the end of the data rows (where there is what appears to be a blank cell but it is in fact a merged cell and may not fit the requirement for being Blank! Could we use the IsEmpty or LEN function to check?

If we choose column B then the range would stop at the first (or possibly second) "Blank" cell leaving the rest as is.

Thank you.

Neil

[Bernie Deitrick]

D'oh! That's what you get when I write code in a reply window and not a workbook.

Anyway, the "A" in this part

WS.Cells(WS.Rows.Count, "A").End(xlUp).Row

can be changed to any column in your range that would accurately reflect the size of your data, since it is reading the row, and ignores the column.

[webwyzard]

Hi Bernie.

I have tried to choose what seems to be a blank cell but it ignores it. I also inserted a new row and it still does not see it as blank?

I am not sure what constitutes a blank cell now.

I also copied the cell the routine currently expands the range to, up to where it should stop and again it ignored it? I am probably missing something here, any ideas?

EDIT: I could also choose to find the row that has a cell with "Closed P/L:" in it and come back up three rows if that makes it simpler?

Thanks

Neil

[Bernie Deitrick]

I'm not sure what code you are using, or what the structure is of your workbook, which certainly impacts how code needs to be written. Why did you insert a blank row? If you go to the bottom-most cell of a column in your data range and press End DownArrow, does it take you to another cell or to row 1048576? Find a column that does take you to that row, and post it... or post an actual workbook that you are using (Sanitized if needed, but with the same structure and layout).

[webwyzard]

Hi Bernie

The code i am using is yours posted earlier in this thread.

What i noticed is in using WS.Cells(WS.Rows.Count, "A").End(xlUp).Row is that it jumps over what appears to be a blank cell on to the end of separate few rows of data i do not want included. So it appears that cell is not really blank.

I have found a way to iterate down the rows to a first non numeric cell in the meantime and that allows me to count the rows and so select the correct range.

Not sure why the other code does not work for me.

Regards

Neil

[Bernie Deitrick]

Does your workbook have a Table - not just a set of data, but a range that you converted to an Excel data table using Insert / Table? That changes how code interacts with a worksheet.

[webwyzard]

The workbook is an imported htm file that i open by just selecting and clicking it.

I suspect that it must have some formatting left in the cell but i can not see it.

Even adding a row that i would assume to be blank does not work.

My crude code to overcome this is below. I inserted it in your code to get lngC value. I am not an excel coder as you will see but it seems to work

Please Login or Register  to view this content.

[Bernie Deitrick]

I would suggest not using select at all, and you may be missing the last few cells by using .Count after offsetting by 4 rows. Try changing your code to

Please Login or Register  to view this content.