Select Case and Delete rows

[labrooy]

I need some help fixing my errors. I am trying to automate a repetitive daily task.

At present, a vlookup is used in column J to return values from another sheet, then column J is used as the basis for further actions to be done.
For example, some rows are deleted based on the text shown in column J, and some cells in column I have the corresponding value in Column J pasted into them.

I want to perform both these tasks using a macro.

If it makes a difference - the values in column J are always vlookup formula results, rather than the text values which are shown in the sample file.

Please Login or Register  to view this content.

Thanks for your help

[protonLeah]

Please Login or Register  to view this content.

[pike]

or with your code

Please Login or Register  to view this content.

[labrooy]

Thanks for the quick reply. I added your code and tested it. It works but not quite how I want it. It does not delete the final row where column J has "Delete row".

Also, I only want it to delete rows where the value starts with "Delete row -" and leave rows starting with "Delete row w". I tried to code this using what I thought was the wildcard * but it is not working.

Please Login or Register  to view this content.

[rscsmith]

Hi

Here's my version:

Please Login or Register  to view this content.

Best regards, Rob.

[labrooy]

Thanks pike, that did exactly what I needed, just had to add a hyphen in the appropriate place.

Thank you both for the quick response and efforts.

[rscsmith]

Like I was way tooooo slloooowww.

[labrooy]

Thank you Rob for the alternative and all the useful explanations !

[pike]

never to slow you probably dont have broad band in sydney

[labrooy]

I am running the macro and getting a Runtime Error 13 Type mismatch

In pike's code it is at line: If InStr(1, Range("J" & xrow).Value, "Delete row") > 0 Then

In Rob's code it is at line: Case "CWA000000" To "CWA999999"

[snb]

Please Login or Register  to view this content.

[pike]

When you click a macro or you click a function on a menu that was created by another macro, you may receive the following error message:
Runtime Error 13 Type mismatch
have you changed the data from the example workbook?

[labrooy]

The data from the example workbook is different to the data in my real workbook -the real workbook has vlookup formula in the relevant cells.



snb - thanks I will take a look at that alternative too.

[labrooy]

snb - your code does not work on the vlookup cells. I could change the vlookup values to text, but I would need to amend another macro to then add the vlookup again. I'm not sure if it is easier to amend one of the macro's everyone has provided, or copy/paste the vlookup results.

It returns an error on line Columns(10).SpecialCells(4).EntireRow.Delete if there are no rows to delete. This should not be a problem as theoretically there will always be rows to delete.

It also returns a run-time error 1004 Application/object defined error on line sn = Columns(10).SpecialCells(2).Resize(, 2)

[pike]

Hi labrooy,
I cant replicate the errors

[labrooy]

I have managed to successfully run your code and Rob's code on a new sample file with vlookups included.

On the real file, your code still gives me a Type 13 error, after successfully deleting all the "Delete row" values it gives the error on the same InStr line.

Please Login or Register  to view this content.

If it helps, the Yellow Debug highlighting stops at the end of Then

[pike]

can you zip and attach the problem workbook?

[pike]

just tried the code with 1000 lines an cant reproduce the error??

[labrooy]

Workbook attached. I have tested the 3 macro solutions in this workbook and receive errors for 2 of them and the other one does not seem to work as intended.

[pike]

The error in the cells is causing the problem

use

Please Login or Register  to view this content.

[labrooy]

Thanks pike.

The new version deletes all the rows where column J = #N/A

[pike]

cant remember if 2003 has iferror
but if you change the formula to display blank in stead of #N/A

Please Login or Register  to view this content.

[snb]

Please Login or Register  to view this content.