View Full Version : Solved: Delete Rows based on Criteria
joshatse
04-30-2007, 02:31 AM
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
04-30-2007, 02:39 AM
Are all of those words in different cells?
joshatse
04-30-2007, 02:49 AM
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
04-30-2007, 02:52 AM
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
04-30-2007, 03:28 AM
Sorry, I forgot that the row A5 is a merged row with few cells containing the text.
Thanks a lot !!
Bob Phillips
04-30-2007, 03:49 AM
So does that mean that my code doesn't work?
joshatse
05-01-2007, 06:42 PM
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
05-01-2007, 08:09 PM
I would try to eliminate the merged cells....they are going to cause more problems than they fix....use center across selection instead.
joshatse
05-03-2007, 11:58 PM
/Solved
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.