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

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

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

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

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

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