Solved: Delete Rows based on Criteria

[joshatse]

Hi,

Assume my spreadsheet contains the following data:

A B C D
1 xx xx xx xx
2 xx xx xx xx
3 xx xx xx xx
4 xx xx xx xx
5 This is a Test
6 xx xx xx xx
7 xx xx xx xx
8 xx xx xx xx

I would like to delete all the rows above the search text "This is a Test" using the macro and move the rows up.

Thanks for your help !!

/Mahesh

[Bob Phillips]

Are all of those words in different cells?

[joshatse]

The Cell A5 contains the text "This is a Test" and I wish to delete the rows from A1 to A4. The text "This is a Test" will occur only once in the spreadsheet. The different cells contains difference information and to make it easier, I used "xx".

[Bob Phillips]

Public Sub Test()
Const sFind As String = "This is a Test"
Dim oCell As Range

Set oCell = Columns(1).Find(sFind)
If Not oCell Is Nothing Then
If oCell.Row > 1 Then
Rows("1:" & oCell.Row - 1).Delete
End If
End If
End Sub

[joshatse]

Sorry, I forgot that the row A5 is a merged row with few cells containing the text.

Thanks a lot !!

[Bob Phillips]

So does that mean that my code doesn't work?

[joshatse]

It works, if I manually unmerge the cells from A5 to D5.

It doesn't work if the row A5 is a merged row.

[lucas]

I would try to eliminate the merged cells....they are going to cause more problems than they fix....use center across selection instead.

[joshatse]

/Solved