0

I have an excel sheet with a few thousand rows of something similar to the following:

A1 B1

9-1 Apple

9-2 Pineapple

10-1 Grape

10-2 Orange

10-3 Pear

11-1 Lemon

Is there a VBA script that can delete the rows which contain anything other than a "-1" after the number? So the result should look like this:

A1 B1

9-1 Apple

10-1 Grape

11-1 Lemon

The number of variations for each first digit varies. Sometimes it can be only 1, while other times it can be up to 8 or 9. Just need to keep the "-1" of every row and delete the rest.

Many thanks in advance.

1 Answer 1

0

Here's a quick and dirty way to do it:

  1. In C1 (or your first available column) enter the formula =FIND(-1,A1). Double-click or drag the cell handle to copy the formula all the way down to all rows. Now the cells containing -1 should have a number, and the others should say #VALUE!.

  2. Select the entire column C and press Ctrl+F. Click Options. Type #value! into Find what and change the Look in: box to Values and hit Find All. Then press Ctrl+A, right-click on one of the highlighted cells, select Delete, change to Entire row and hit OK.

2
  • Thanks for your response...is there a VBA way of doing so? It's just that I have dozens of separate sheets and would like to trigger a some sort of quick hotkey for each.
    – Michael
    Mar 14, 2017 at 2:49
  • My VBA skills are very rudimentary but I'm sure it's doable. By the way, I just edited my answer to remove an unneeded step.
    – wysiwyg
    Mar 14, 2017 at 5:21

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .