Delete Rows Between two Columns

[fahadmalik09]

Hey Team,

Experience

V1

V2

Mobile No:

Experience

V3

v4

v5

Mobile No:

Experience

V6

Mobile No:

Experience

V7

v8

v9

Mobile No:

<tbody>
</tbody>

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
1	Name : XYS
2	Age : 38
3	Experience
4	Done Diploma
5	Engineering in CS
6	Mobile No : 123456789
7	Address : cb club gate
8
9	Name : ABC
10	Age : 42
11	Experience
12	Done Masters
13	Doing PHD
14	Certificate course in Electronics
15	Mobile No : 123456789
16	Address : River Valley

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

Sheet1​

I end up with

Excel 2013/2016

	A
1	Name : XYS
2	Age : 38
3	Mobile No : 123456789
4	Address : cb club gate
5
6	Name : ABC
7	Age : 42
8	Mobile No : 123456789
9	Address : River Valley

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
</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
1	Name : XYS
2	Age : 38
3	Mobile No : 123456789
4	Address : cb club gate
5
6	Name : ABC
7	Age : 42
8	Mobile No : 123456789
9	Address : River Valley

<tbody>
</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
1	Name : XYS
2	Age : 38
3	Experience
4	Done Diploma
5	Engineering in CS
6	Mobile No : 123456789
7	Address : cb club gate
8
9	Name : ABC
10	Age : 42
11	Experience
12	Done Masters
13	Doing PHD
14	Certificate course in Electronics
15	Mobile No : 123456789
16	Address : River Valley

<tbody>
</tbody>

Sheet1​

I end up with

Excel 2013/2016

	A
1	Name : XYS
2	Age : 38
3	Mobile No : 123456789
4	Address : cb club gate
5
6	Name : ABC
7	Age : 42
8	Mobile No : 123456789
9	Address : River Valley

<tbody>
</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.