Delete data based on condtion

[smartguy]

Hello all,

Good day. I have excel file in the below format.

If Condition "ZJ" i need to remove entire row .

For example :

"O2" contain "ZJ" i need to remove from "2" to "12" Row.

"O21" contain "ZJ" i need to remove from "21" to "26" Row.


Please help Excel MVP & Excel expert.




Excel 2013/2016

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Socument no
2	10														ZJ
3		sasa
4			sasa
5			sasa
6			sasa
7			sasa
8			sasa
9			sasa
10			sasa
11
12
13	20	sasa
14			sasa	sasa	sasa	sasa
15	30
16
17	40
18
19
20
21	50														ZJ
22		sasa	saa	sa	sa
23			saa
24						sasa
25						sasa
26						sasa
27	60
28
29		sasa
30				sasa

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

 

[MickG]

Try this:-
NB:- This code will alter your sheet data !!!

[COLOR="Navy"]Sub[/COLOR] MG28Mar15
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, R [COLOR="Navy"]As[/COLOR] Range, nRng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range("A:A").SpecialCells(xlCellTypeBlanks)
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng.Areas
    [COLOR="Navy"]If[/COLOR] Dn(1).Offset(-1, 14) = "ZJ" [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]Set[/COLOR] R = Dn.Offset(-1).Resize(Dn.Count + 1)
        [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Set[/COLOR] nRng = R Else [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, R)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]If[/COLOR] Not nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] nRng.EntireRow.Delete
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[smartguy]

Thank you Mickg

Your my life saver....

 

[smartguy]

Hi Mickg,

If there is any blank column in B or C Column macro is not deleting entire row...

macro missed the blank row.

Please help.

 

[MickG]

I can't repeat that, please show example of data that fails.

 

[smartguy]

Sorry for late reply...

Macro working great without error.

 

[MickG]

Good news !!!