Delete Rows Between two Columns

[fahadmalik09]

Hey Team,

[TABLE="class: grid, width: 50"]
<tbody>[TR]
[TD]Experience[/TD]
[/TR]
[TR]
[TD]V1[/TD]
[/TR]
[TR]
[TD]V2[/TD]
[/TR]
[TR]
[TD]Mobile No:[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Experience[/TD]
[/TR]
[TR]
[TD]V3[/TD]
[/TR]
[TR]
[TD]v4[/TD]
[/TR]
[TR]
[TD]v5[/TD]
[/TR]
[TR]
[TD]Mobile No:[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Experience[/TD]
[/TR]
[TR]
[TD]V6[/TD]
[/TR]
[TR]
[TD]Mobile No:
[/TD]
[/TR]
[TR]
[TD]Experience[/TD]
[/TR]
[TR]
[TD]V7[/TD]
[/TR]
[TR]
[TD]v8[/TD]
[/TR]
[TR]
[TD]v9[/TD]
[/TR]
[TR]
[TD]Mobile No:[/TD]
[/TR]
</tbody>[/TABLE]

I want to delete Values between "Experience" to "Mobile:", ie v1, v2 , v3, v4 .......
Problem is , some has single row below "Experience", some has more than one row below "Experience"

How to delete

 

[fahadmalik09]

Dear Rick,

Yes Mobile1 , Mobile2 are real numbers
The only common word is "experience"

like

A1 Name : XYS
A2 Age : 38
A3 Experience
A4 Done Diploma
A5 Engineering in CS
A6 Mobile No : 123456789
A7 Address : cb club gate
A8 Mobile No : 987654321
A9
A10 Name : ABC
A11 Age : 42
A12 Experience
A13 Done Masters
A14 Doing PHD
A15 Certificate course in Electronics
A16 Mobile No : 123456789
A17 Address : River Valley
A18 Mobile No : 123456789

 

[Fluff]

Does the word "Mobile" actually exist as the first word in the cell?
Also do all groups have this as the last 3 lines
Mobile No : ????
Address : ????
Mobile No : ????


or can the be other variations?

 

[fahadmalik09]

Yup
"Mobile No :" is common word in all cell
"Address :" is common word in all cell

Looks like

Mobile No : 987654321
Address : RIver Valley , NJ

Mobile No: 123456790
Address : Park Avenue , Au

"Mobile No: " and "Address :" are the first Word which is common

 

[Fluff]

If I start of with

Excel 2013/2016

	A
Name : XYS
Age : 38
Experience
Done Diploma
Engineering in CS
Mobile No : 123456789
Address : cb club gate
Name : ABC
Age : 42
Experience
Done Masters
Doing PHD
Certificate course in Electronics
Mobile No : 123456789
Address : River Valley

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]13[/TD]

[TD="align: center"]14[/TD]

[TD="align: center"]15[/TD]

[TD="align: center"]16[/TD]

</tbody>

Sheet1​

I end up with

Excel 2013/2016

	A
Name : XYS
Age : 38
Mobile No : 123456789
Address : cb club gate
Name : ABC
Age : 42
Mobile No : 123456789
Address : River Valley

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

</tbody>

Master​

Are your values in col A & do they look EXACTLY like the samples you have given?

 

[fahadmalik09]

Dear Rick,

Yes Mobile1 , Mobile2 are real numbers
The only common word is "experience"

like

A1 Name : XYS
A2 Age : 38
A3 Experience
A4 Done Diploma
A5 Engineering in CS
A6 Mobile No : 123456789
A7 Address : cb club gate
A8
A9 Name : ABC
A10 Age : 42
A11 Experience
A12 Done Masters
A13 Doing PHD
A14 Certificate course in Electronics
A15 Mobile No : 123456789
A16 Address : River Valley


Some correction of above comment.

 

[fahadmalik09]

Yippee....Thats what i want....

If I start of with

​

I end up with

Excel 2013/2016

	A
Name : XYS
Age : 38
Mobile No : 123456789
Address : cb club gate
Name : ABC
Age : 42
Mobile No : 123456789
Address : River Valley

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

</tbody>

 

[Fluff]

That's what my code in post#8 does!

If that doesn't work for you is your data EXACTLY the same as I used?
What error message do you get 7 on what line?

 

[fahadmalik09]

Perfect....This is super perfect. You guys are super heroes.
Exactly that i need...

If I start of with

Excel 2013/2016

	A
Name : XYS
Age : 38
Experience
Done Diploma
Engineering in CS
Mobile No : 123456789
Address : cb club gate
Name : ABC
Age : 42
Experience
Done Masters
Doing PHD
Certificate course in Electronics
Mobile No : 123456789
Address : River Valley

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]13[/TD]

[TD="align: center"]14[/TD]

[TD="align: center"]15[/TD]

[TD="align: center"]16[/TD]

</tbody>

Sheet1​

I end up with

Excel 2013/2016

	A
Name : XYS
Age : 38
Mobile No : 123456789
Address : cb club gate
Name : ABC
Age : 42
Mobile No : 123456789
Address : River Valley

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

</tbody>

Master​

Are your values in col A & do they look EXACTLY like the samples you have given?

 

[fahadmalik09]

Runtime error 13
If Rng.Offset(Rng.Count - 2).Resize(1, 1).Value Like "Mobile*" Then

Yellow arrow on left side

That's what my code in post#8 does!

If that doesn't work for you is your data EXACTLY the same as I used?
What error message do you get 7 on what line?

 

[Fluff]

That suggests that you have values like #N/A, #NA ME?, #VALUE ! or similar in one or more of your cells.