Look for cell containing "Initial" then if the next cell after equals "Final" then delete both rows.
 

Hi everyone

I pretty much summed everything up above.

Thousands of rows of data most of which is useless but I thought the
best way to delete would be to find in column A the word "Initial" and
if the cell immediately after is "Final" delete both rows. If not
then this is a good row and it should continue until it finds the next
occurrence of "Initial"

I hope I make sense,

Thanks alot in advance,

Andrea

Look for cell containing "Initial" then if the next cell after equ
 

Maybe something like this. It goes bottom to top and looks for Final first.
If the cell above it says "Initial" then both rows are deleted.

Sub test()
Dim lRow As Long
Dim l As Long

lRow = Range("A65536").End(xlUp).Row

For l = lRow To 2 Step -1
If Range("A" & l).Value = "Final" And Range("A" & l - 1).Value =
"Initial" Then
Range(Range("A" & l), Range("A" & l - 1)).EntireRow.Delete
l = l - 1
End If
Next l

End Sub

--
Hope that helps.

Vergel Adriano


" wrote:

Hi everyone

I pretty much summed everything up above.

Thousands of rows of data most of which is useless but I thought the
best way to delete would be to find in column A the word "Initial" and
if the cell immediately after is "Final" delete both rows. If not
then this is a good row and it should continue until it finds the next
occurrence of "Initial"

I hope I make sense,

Thanks alot in advance,

Andrea

Look for cell containing "Initial" then if the next cell after equ
 

Sub Macro1()
Dim rng as Range, r as Range
Dim sAddr as String
Set rng = Columns(1).Find(What:="B", _
After:=Range("A65536"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

if not rng is nothing then
sAddr = rng.Address
do
if rng.offset(1,0) = "Final" then
if r is nothing then
set r = rng.Resize(2,1)
else
set r = Union(r,rng.Resize(2,1)
end if
end if
set rng = columns(1).FindNext(rng)
Loop while rng.Address < sAddr
if not r is nothing then
r.EntireRow.Delete
End if
end Sub
--
Regards,
Tom Ogilvy

" wrote:

Hi everyone

I pretty much summed everything up above.

Thousands of rows of data most of which is useless but I thought the
best way to delete would be to find in column A the word "Initial" and
if the cell immediately after is "Final" delete both rows. If not
then this is a good row and it should continue until it finds the next
occurrence of "Initial"

I hope I make sense,

Thanks alot in advance,

Andrea

Look for cell containing "Initial" then if the next cell afterequals "Final" then delete both rows.
 

One way is to just start at the bottom and go to the top and look for that
matching pair:

Option Explicit
Sub testme01()

Dim wks As Worksheet
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim DelRng As Range

Set wks = Worksheets("sheet1")

With wks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
If LCase(.Cells(iRow, "A").Value) = LCase("initial") _
And LCase(.Cells(iRow + 1, "A").Value) = LCase("final") Then
If DelRng Is Nothing Then
Set DelRng = .Cells(iRow, "A").Resize(2, 1)
Else
Set DelRng = Union(DelRng, .Cells(iRow, "A").Resize(2, 1))
End If
End If
Next iRow
End With

If DelRng Is Nothing Then
'no matches
Else
'.Select to test and verify. .Delete to do the real deleting.
DelRng.EntireRow.Select '.Delete
End If

End Sub





" wrote:

Hi everyone

I pretty much summed everything up above.

Thousands of rows of data most of which is useless but I thought the
best way to delete would be to find in column A the word "Initial" and
if the cell immediately after is "Final" delete both rows. If not
then this is a good row and it should continue until it finds the next
occurrence of "Initial"

I hope I make sense,

Thanks alot in advance,

Andrea

--

Dave Peterson