Heather,

try this. Set Sht to the correct worksheet

Sub non_Vin()
Dim LastRow As Long
Dim CopyRange As Range
Dim x As Long
Set sht = Sheets("Sheet1") ' Change to suit
LastRow = sht.Cells(Rows.Count, "A").End(xlUp).Row

For x = 1 To LastRow
If InStr(1, sht.Cells(x, 1).Value, "Collateral", vbTextCompare) = 1 Then
If InStr(1, sht.Cells(x + 1, 1).Value, "Collateral", vbTextCompare)
< 1 Then
If CopyRange Is Nothing Then
Set CopyRange = Rows(x + 1)
Else
Set CopyRange = Union(CopyRange, Rows(x + 1))
End If
End If
End If
Next
If Not CopyRange Is Nothing Then
CopyRange.Delete
End If
End Sub

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"HeatherJ" wrote:

I have a large worksheet full of data. Below are three rows of COLUMN A as
an example. Every time Column A has a cell in it which starts with
"Collateral..." can a macro look into the NEXT row (Column A) and see if it
starts with "VIN..."? If so, great--if not, I would like the macro to delete
that row (which does not start with "VIN...".

Is this possible?

COLUMN A
Collateral: 2006 AIRSTREAM SAFARI
16-30FT TRAVEL TRAILER
VIN: 1STJxxxxxxxxxxxxx

Thank you! Heather