CODEWITHSUNDEEP
vbaexcel
eathapeking
eathapeking

Reputation: 329

Excel search and delete different specific cell that does not contain search value

i have to ask that how can search the value and ! delete the differents

if i have data like this below in column A B C and in cell A1 from sheet(2) have "bbb"

what function or VBA should i use to get rid of cell in column A and B on row that not contain bbb

try using macro record but it wont work well

      A                   B                       C

aaa-1.1.1.1         aa1a1a1a1a1               remark

bbb-2.5.2.2         b2b2b2b2b2b2              remark  

ccc-3.3.3.3         c3c3c3c3c3c3              remark

bbb-1.2.2.5         b2b2b2b2b2b2              remark  

ddd-4.1.2.4         d4d4d4d4d4d4              remark

bbb-1.3.2.7         b2b2b2b2b2b2              remark  

bbb-2.2.2.2         b2b2b2b2b2b2               remark

result should be like 

    A                  B                        C
                                              remark

bbb-2.5.2.2         b2b2b2b2b2b2              remark  

                                              remark

bbb-1.2.2.5         b2b2b2b2b2b2              remark  

                                              remark

bbb-1.3.2.7         b2b2b2b2b2b2              remark  

bbb-2.2.2.2         b2b2b2b2b2b2              remark

Thank you for every help i can get :)

Upvotes: 0

Views: 201

Answers (2)

StoriKnow
StoriKnow

Reputation: 5866

If you're not looking for an automated solution (e.g. this is a one-and-done scenario, no programming or recording required), you could simply sort the data based on Column A's values, then the deletion process is trivial. In short:

  1. Highlight all rows/columns in question
  2. Right click -> Sort -> Sort A to Z
  3. Select all rows above and below rows that contain bbb. This can be done by holding CTRL while making a selection.
  4. Press delete

Here's some screen shots of these steps and the result

Sort the data enter image description here

Select rows above and below bbb enter image description here

Press the Delete key (Result) enter image description here

Upvotes: 1

Paul
Paul

Reputation: 46

Use vba

The following would stop when it gets to an empty line... 

x = 1
While Cells(x, 1) <> ""
    If Left(Cells(x, 1), 3) <> "bbb" Then
        Cells(x, 1) = ""
        Cells(x, 2) = ""
    End If
    x = x + 1
Wend

Upvotes: 1

Related Questions