Delete rows in excel with specific words

There is no single command that will provide you such result. 

You will need to use a combination of commands.

Firs, let’s break the problem in 2 parts:

1. Scan Excel for “OLD” Values
2. Delete or Blank Excel rows with “OLD” values

• Scan Excel for OLD Values

To load the Excel file in memory, you will need to use “Copy From Excel”

As you can see, all rows in Excel are now in Memory.

After loading the file in memory, locate each row that contain value “OLD” and store the row number in a new array to be used later, using the command Table Lookup

Here we can see the results  of the search, on the left side of the above image:

• Column 1 - Row 5
• Column 1 - Row 17
• Column 1- Row 22.

and Here is our Excel.

• Delete or Blank Rows

If you are looking to just blank the values of all rows that contain OLD, you should use “Delete from Excel” and run such command for each row that contains “OLD”.

Results:

Now, if you are looking to remove the entire row, where OLD is located, you should use Excel Row Actions command

I also have attached the Wizard sample: Wizard_Delete_Excel.zip. Please unzip it before importing it.

Enjoy!

Hello Fernando!

Thanks for the assistance! I am unable to open your zip file as it is encrypted.

I am stuck at deleting the rows. Is there any mistake on any part in particular to delimiter and the row index? I have tried adjusting the row index but the rows still did not get deleted.

Regards,

Shin

The file is not encrypted.

Please unzip it using windows default extractor.

Also, I am attaching the .dwiz file with the extension .txt. Please remove the .txt before importing it in Kryon Studio.

In regards to your question, I do not know how your array looks like, but seems like the delimiter for loop items should be a |(pipe) instead of a ^(caret).

Please try that.

Hi Fernando

I have changed to exactly mirror your example so I guess it is using the same array. May I know if the highlighted inputs are correct? I have changed to | (pipe) but still did not get the same results as you. I am using the Excel row commands.

I cannot open the zipped as it seems to be blocked by my company’s security. Apologies.

Regards,

Shin

Hello,

No need to change the “get Arrary Data” That typicaly should use a caret ^ instead of |

such configuration is denfined in the following command(Copy from Excel) during the scan phase:

Column Delimiter: ^

Row Delimiter: |

Also,you will need to use a variable in Parameter “From row index” instead of a fixed value.

In the example I shared, the variable name is $myrow$.

Please try that.

Hi

I have tried your suggestions but still did not get the results.

I am mirroring your variables exactly.

Hi @fguaragna any more ideas on this one? 

Thanks! It works now.

Great to hear that it works now!