| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete rows containing Vendor Total A,B,C etc
Hi
I am looking for a macro that will delete entire rows from a report containing the phrase 'Vendor Total' followed by Company name. Here is an example of the raw data:- Company Apple Vendor Total Company Apple Vendor Total Company Banana Vendor Total Company Banana Company Orange Company Apple Vendor Total Comapany Pear Once the macro has run and deleted out the entire lines containg Vendor Total, the following data should remain:- Company Apple Company Orange Company Apple There is a header in row 1 that should remain. Thanks |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Delete rows containing Vendor Total A,B,C etc
try the below code
Worksheets("Sheet2").Activate Dim i As Integer Dim LastRow As Long LastRow = Range("A65000").End(xlUp).Row For i = 2 To LastRow If InStr(1, Range("A" & i).Value, "Vendor Total") Then Rows(i).Delete Next "iansmigger" wrote: Hi I am looking for a macro that will delete entire rows from a report containing the phrase 'Vendor Total' followed by Company name. Here is an example of the raw data:- Company Apple Vendor Total Company Apple Vendor Total Company Banana Vendor Total Company Banana Company Orange Company Apple Vendor Total Comapany Pear Once the macro has run and deleted out the entire lines containg Vendor Total, the following data should remain:- Company Apple Company Orange Company Apple There is a header in row 1 that should remain. Thanks |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Delete rows containing Vendor Total A,B,C etc
Sub deleterows()
RowCount = 2 Do While Not IsEmpty(Cells(RowCount, "A")) Set myrow = Range("A" & RowCount).EntireRow Set c = myrow.Find("Vendor Total", LookIn:=xlValues) If Not c Is Nothing Then c.EntireRow.Delete Else RowCount = RowCount + 1 End If Loop End Sub "iansmigger" wrote: Hi I am looking for a macro that will delete entire rows from a report containing the phrase 'Vendor Total' followed by Company name. Here is an example of the raw data:- Company Apple Vendor Total Company Apple Vendor Total Company Banana Vendor Total Company Banana Company Orange Company Apple Vendor Total Comapany Pear Once the macro has run and deleted out the entire lines containg Vendor Total, the following data should remain:- Company Apple Company Orange Company Apple There is a header in row 1 that should remain. Thanks |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Delete rows containing Vendor Total A,B,C etc
Your for loop will not work. Not all the rows will get deleted especialy is
the Vendor Total is on consecutive lines. row 5 Vendor Total Company Apple <= will get deleted row 6 Vendor Total Company Banana <= gets skipped row 7 Vendor Total Company Banana <= will get deleted "Pranav Vaidya" wrote: try the below code Worksheets("Sheet2").Activate Dim i As Integer Dim LastRow As Long LastRow = Range("A65000").End(xlUp).Row For i = 2 To LastRow If InStr(1, Range("A" & i).Value, "Vendor Total") Then Rows(i).Delete Next "iansmigger" wrote: Hi I am looking for a macro that will delete entire rows from a report containing the phrase 'Vendor Total' followed by Company name. Here is an example of the raw data:- Company Apple Vendor Total Company Apple Vendor Total Company Banana Vendor Total Company Banana Company Orange Company Apple Vendor Total Comapany Pear Once the macro has run and deleted out the entire lines containg Vendor Total, the following data should remain:- Company Apple Company Orange Company Apple There is a header in row 1 that should remain. Thanks |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Delete rows containing Vendor Total A,B,C etc
I think your may need to run this loop backwards to make it work step -1
"Pranav Vaidya" wrote: try the below code Worksheets("Sheet2").Activate Dim i As Integer Dim LastRow As Long LastRow = Range("A65000").End(xlUp).Row For i = 2 To LastRow If InStr(1, Range("A" & i).Value, "Vendor Total") Then Rows(i).Delete Next "iansmigger" wrote: Hi I am looking for a macro that will delete entire rows from a report containing the phrase 'Vendor Total' followed by Company name. Here is an example of the raw data:- Company Apple Vendor Total Company Apple Vendor Total Company Banana Vendor Total Company Banana Company Orange Company Apple Vendor Total Comapany Pear Once the macro has run and deleted out the entire lines containg Vendor Total, the following data should remain:- Company Apple Company Orange Company Apple There is a header in row 1 that should remain. Thanks |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Delete rows containing Vendor Total A,B,C etc
Thant works a treat!
Thanks "Joel" wrote: Sub deleterows() RowCount = 2 Do While Not IsEmpty(Cells(RowCount, "A")) Set myrow = Range("A" & RowCount).EntireRow Set c = myrow.Find("Vendor Total", LookIn:=xlValues) If Not c Is Nothing Then c.EntireRow.Delete Else RowCount = RowCount + 1 End If Loop End Sub "iansmigger" wrote: Hi I am looking for a macro that will delete entire rows from a report containing the phrase 'Vendor Total' followed by Company name. Here is an example of the raw data:- Company Apple Vendor Total Company Apple Vendor Total Company Banana Vendor Total Company Banana Company Orange Company Apple Vendor Total Comapany Pear Once the macro has run and deleted out the entire lines containg Vendor Total, the following data should remain:- Company Apple Company Orange Company Apple There is a header in row 1 that should remain. Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Look up and match Vendor name in one cell of worksheet from list ofmultiple Vendor names in column of other worksheet | Excel Worksheet Functions | |||
| Creating a Vendor ID from Vendor Name... | Excel Worksheet Functions | |||
| Delete Rows that Contain the Text "Total" and vice versa | Excel Programming | |||
| Delete every 3rd row, then delete rows 2-7, move info f/every 2nd row up one to the end and delete the row below | Excel Programming | |||
| Search "Total" in all worksheets and delete rows containing "Total" | Excel Programming | |||
Linear Mode
