asked on

VBA delete rows based on phrase

I have a vba code to name ranges.

However, to work correctly, the phrase it identifies "Continental U.S. Ground" has to be 2 lines above the phrase "Weight (in Lbs )".
Is there any way to identify these two phrases in Column A and remove any additional lines between them?

Needs to be in column A on sheet "Net Rates 1"
"Continental U.S. Ground"
-EMPTY ROW-
"Weight (in Lbs )"

Help??

Ramesh V

Can you send me data?

Euro5

ASKER

The data could be any number of rows in between.
I think I also need to run remove merge and wrap also to get this to work consistently, can you help me do that?

THANK YOU!!
sample.xlsx

Glenn Ray

This code will reset the word wrap and merging anchored on cell A2 and then remove all rows but one between A2 and the top row of the table.

Option Explicit
Sub Clear_Rows()
    With Range("A2")
        .HorizontalAlignment = xlGeneral
        .UnMerge
        .WrapText = False
    End With
    Range("A3").Select
    Do Until ActiveCell.Offset(1, 0).Value = "Weight (in Lbs )"
        ActiveCell.EntireRow.Delete
    Loop
End Sub

Open in new window

Regards,
-Glenn

Euro5

ASKER

Glenn,
The data will not be in the same cells. They could be anywhere in the sheet.
I'm not certain, but it appears that the above assumes that the "Continental U.S. Ground" is in a specific cell?

Euro5

ASKER

Not anywhere in the sheet - they could be in any ROW - the phrases are always in Column A.

Glenn Ray

So, "Continental U.S. Ground" (and it's merged/wrapped cells) could be on any row in column A - then followed further down the sheet by the table?

Could you post another variation of your example that shows other possible configuration(s)? I'm sure it's an easy mod to the example code I provided.

-Glenn

ASKER CERTIFIED SOLUTION