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

Question

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 :)

Try recording a macro using Autofilter and then amend the code? See the use of Autofilter here http://stackoverflow.com/questions/10050946/selecting-columns-that-have-values-in-excel-macro-range-object-in-vba and here http://stackoverflow.com/questions/11631363/how-to-copy-a-line-in-excel-using-a-specific-word-and-pasting-to-another-excel-s — Siddharth Rout, Feb 12 '13 at 04:33

score 1 · Answer 1 · edited Feb 12 '13 at 04:43

1

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

edited Feb 12 '13 at 04:43

Siddharth Rout

147,039
17
206
250

answered Feb 12 '13 at 04:36

Paul

46
3

1

If you have large dataset then looping is considered very slow as compared to autofilter :) – Siddharth Rout Feb 12 '13 at 04:45
1

@SiddharthRout out of curiosity, do you know how much faster autofilter is? - Since OP didn't specify the amount of data then this is a very simple sonlution to the problem. +1. – StoriKnow Feb 12 '13 at 16:26
1

@Sam: That is a valid question. If you have a small dataset then the speed is negligible but if you have large datasets like I mentioned above say 10K rows then you would be looping 10k times whereas the Autofilter will be much faster. If you know how to use timers in Excel then you can actually time it :) – Siddharth Rout Feb 12 '13 at 18:33
where should i put it in in the VBA place that save macro right?? – eathapeking Feb 13 '13 at 03:55
Press alt+f8, pick a macro name, created, edit, paste the code above between sub_macroname() and end sub, then go back to excel, alt+f8 and run your macro – Paul Feb 14 '13 at 05:16

score 1 · Answer 2 · answered Feb 12 '13 at 16:37

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:

Highlight all rows/columns in question
Right click -> Sort -> Sort A to Z
Select all rows above and below rows that contain bbb. This can be done by holding CTRL while making a selection.
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

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

2 Answers2