Macro help
 

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

Macro help
 

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

Macro help
 

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

--


Gary Keramidas
Excel 2003


"Mike H" wrote in message
...
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