Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
Need syntax for RUNning a Word macro with an argument, called from an Excel macro | Excel Programming | |||
how to count/sum by function/macro to get the number of record to do copy/paste in macro | Excel Programming | |||
Start Macro / Stop Macro / Restart Macro | Excel Programming |