Macro help

  • Thread starter Thread starter HeatherJ
  • Start date Start date
H

HeatherJ

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
 
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.
 
here's one way that should work

Option Explicit
Sub remove_rows()
Dim ws As Worksheet
Dim lastrow As Long
Dim i As Long
Dim rowsToDelete As Range
Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
i = 1

Do While i < lastrow
Do While InStr(1, ws.Range("A" & i).Value, "Collateral")
If InStr(1, ws.Range("A" & i).Value, "VIN") Then
Exit Do
Else
If rowsToDelete Is Nothing Then
Set rowsToDelete = Rows(i + 1)
Else
Set rowsToDelete = Union(rowsToDelete, Rows(i + 1))
End If
End If
i = i + 1
Loop
i = i + 1
Loop

If Not rowsToDelete Is Nothing Then rowsToDelete.Delete

End Sub
 
Back
Top