Macro to delete rows apart from specific items from a list.?

Say that we have a text file with the following rows, and this file is called colors.txt.

Red

Green

Blue

Yellow

Orange

White

Black

##############

We can print only the lines which do not contain the colors "Blue" or "Yellow" and the new text file: new_color.txt

will contain all lines except those lines which contain "Blue" or "Yellow", so this is what you get. Writing to a new file is usually better when you are programming so that you don't corrupt your primary data as you process it if you made some errors:

Red

Green

Blue

Yellow

Orange

White

Black

##################### THE SCRIPT #########################

use strict; # Variable scoping.

use warnings; # If anything goes wrong.

use feature 'say';

say "Please enter your filename to open";

chomp(my $input_file = <STDIN>);

my $input_file_handle;

my $outfile = 'new_colors.txt'; # This is your new file with only the rows that do NOT contain the colors "Blue" or "Yellow".

my $outfile_handle;

open($input_file_handle, '<', $input_file) or die "Problem opening file. It probably doesn't exist"; # Open a file for reading.

open($outfile_handle, '>', $outfile) or die "Problem opening file. It probably doesn't exist."; # Open a file to write to.

while(my $row = <$input_file_handle>)

{

if($row !~ m/(Blue|Yellow)/i)

{

say $row; # Say the rows which do not match the color Blue or Yellow to the screen.

print $outfile_handle $row; # Output the same information to a file as well.

}

}

# Finally close the files now that we are done with them. This is good practice.

close $input_file_handle or warn "Wow! I can't even close the input file. That's sad";

close $outfile_handle or warn "Wow! I can't even close the output file. That's sad";

#################

I wrote a program to do this which utilizes a regular expression to search for "Not" Yellow and "Not" Blue.

So if you want to use it you need to install perl, and then put this perl program in your working directory which contains your file that is for example colors.txt.

Save the file in Notepad as "all files (*)" with the added extension .pl for perl file,

and lets say you save it as select_colors.pl.

and then you run it like this from command prompt like so:

erl .\select_colors.pl

Please enter your filename to open

colors.txt

Red

Green

Orange

White

Black

#############

Note: I setup the program so that it accepts standard input, so as long as you have the program in the same folder as your text file with the colors, whatever your filename is called just type it in. In this case, it was colors.txt

You will see a new file was created called new_colors.txt

That's your file which contains all the rows without "Blue" and "Yellow".

(Note: This program doesn't work on Excel files because those are binary with so much encoding. It works on text files and csv files, though.)

The first issue is whether you are actually deleting rows based on fill color, or whether you are deleting be numeric values or text strings. I infer that your example, using colors, is referring to text strings, not fill colors.

I usually prefer to use event handlers, instead of macros, whenever possible. For example, the Before_DoubleClick event will delete the rows with a simple double click of any cell. A macro does the same thing, but you either have to access the Developer tab, click Macros, locate the macro, and 'Run' it, or create a desktop shortcut to run it using Ctrl + a letter. Double clicking is a single action.

Here is a simple event handler that will delete rows based on text strings.

This example assumes the data is in column A, beginning in Row 2.

If your column is not "A", change the "A" reference to your column letter, i.e. "C", "F", etc. If you are not really parsing 'Yellow' and 'Blue', change the search criteria indicators to your actual criteria. If you have more than two indicators, add additional search elements structured as the current two criteria are. The 'UCase' modifier takes 'case' out of the equation. So, if it locates 'Red', 'RED', 'red', or even 'ReD', it will consider those as a match.

Copy this event handler to the clipboard (highlight the entire event handler, right click inside the highlighted area, and 'Copy'):

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim i, LastRow

LastRow = Cells. SpecialCells(xlCellTypeLastCell).Row

For i = LastRow To 2 Step -1

If UCase(Cells(i, "A").Value) <> "YELLOW" And UCase(Cells(i, "A").Value) <> "BLUE" Then

Cells(i, "A").EntireRow.Delete

End If

Next i

Target.Offset(1).Select

End Sub

Select the worksheet containing the data to parse and right click the sheet tab at the bottom.

Select 'View Code'.

Paste the event handler into the white editing area to the right (right click inside the area and 'Paste').

Close the VBE (red button - top right)

Double click any cell to delete the specified rows.

Note: if you wish to retain this functionality when the workbook is reopened in the future, save the workbook as an Excel Macro-Enabled Workbook. If not, click 'Yes' when you save and close the workbook. This will delete the event handler from the workbook.

Have a look at https://analysistabs.com/excel-vba/delete-rows-col...