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

 

[Fluff]

If you only want to keep the those two values try

Sub DelRws()
   With Range("A1", Range("A" & Rows.Count).End(xlUp))
      .Replace "Experience", "=xxxExperience", xlWhole, , False, , False, False
      .Replace "Mobile No:", "=xxxMobile No", xlWhole, , False, , False, False
      .SpecialCells(xlConstants).EntireRow.Delete
      .Replace "=xxxExperience", "Experience", xlWhole, , False, , False, False
      .Replace "=xxxMobile No", "Mobile No:", xlWhole, , False, , False, False
   End With
End Sub

 

[fahadmalik09]

First of all, thanks for the reply,

Only left with Experience , it works but whole sheet is deleted.
Kindly let me describe my scenario once again

Name1
Age1
Experience
v1
v2
v3
Mobile No1
Address1

Name2
Age2
Experience
v4
Mobile No2
Address2

Name3
Age3
Experience
v5
v6
v7
v8
Mobile No3
Address3

I want

Name1
Age1
Mobile1
Address1

Name2
Age2
Mobile2
Address2

Name3
Age3
Mobile3
Address3

Hope, you genius guys can understand , what i am trying to say

 

[Fluff]

Do you have blank cells between each group as shown?
and will "Mobile" always be the last line for each group?

 

[Rick Rothstein]

...and will "Mobile" always be the last line for each group?

Based on Message #3 , it looks like "Mobile" will not be the last line in each group so your question about the blanks between groups picks up added importance.

 

[Fluff]

The OP added the "Address" line to the post at the same time as I posted.
So that answers 1 of my questions.
But raises another.


Will there only ever be one line after the "Mobile" line & will it always be there?

 

[fahadmalik09]

A1 Name1
A2 Age1
A3 Experience
A4 V1
A5 v2
A6 v3
A7 Mobile1
A8 Address1
A9
A10 Name2
A11 Age2
A12 Experience
A13 V4
A14 v5
A15 Mobile2
A16 Address2


I want

A1 Name1
A2 Age1
A3 Mobile1
A4 Address1
A4
A5 Name2
A6 Age2
A7 Mobile2
A8 Address2

I tried my best to explain, apologise for troubling you guys.

I want to delete Experience as we as v1,v2,v3,.... values before Mobile

 

[Fluff]

In that case try

Sub DelRws()
   Dim Ar As Areas, Rng As Range
   With Range("A1", Range("A" & Rows.Count).End(xlUp))
      .Replace "Experience", "", xlWhole, , False, , False, False
      Set Ar = .SpecialCells(xlConstants).Areas
   End With
   For Each Rng In Ar
      If Rng.Offset(Rng.Count - 2).Resize(1, 1).Value Like "Mobile*" Then
         Rng.Offset(-1).Resize(Rng.Count - 1).EntireRow.Delete
      End If
   Next Rng
End Sub

 

[fahadmalik09]

It shows error Debug, i end up and run the code, it deleted the Experience word from the sheet

Showing error on this line below
If Rng.Offset(Rng.Count - 2).Resize(1, 1).Value Like "Mobile*" Then

Mobile 1, mobile2, mobile3 are all different values, as i mentioned earlier.

Thanks for the reply, but still stuck

 

[Rick Rothstein]

I want to delete Experience as we as v1,v2,v3,.... values before Mobile

Is Mobile1, Mobile2, etc. the words "Mobile1", "Mobile2", etc. or are those actually mobile telephone numbers?