Trying to create a macro to delete entire rows based on cell value that is variable in length and characters

[tribalbydesign]

Hello,

I am trying to create a macro to use on a speadsheet that checks the value of a cell in Column A and if it meets a criteria (which is variable) then it will delete the entire row.

The contents of the cells is below. The "Pc. Price:" part does not change, however the price does. The price can be from a whole number with no decimal point to having thousandths of a cent (i.e. .003).

The Pc. Price, Total Value, Total Pieces, Total Cartons, and Delivery address are the only things I want to be deleted. I can not figure out how to delete a line based on part of the cell value. The information is electronically fed to me so I have no choice in how it shows up on the excel worksheet. (I tried copy and paste into excel to hopefully pull the information apart, however last time I tried that it all went into column A anyways.)

Each row of information below is in 1 cell together, the decription of the value, and the value.

Pc. Price: 0.03
Total Line Value: 300
Line I want to Keep
Line I want to Keep
Line I want to Keep
Total Pieces: 10000
Total Cartons: 100
Line I want to Keep
Line I want to Keep
Delivery to: adress with letters, numbers, and dash's possible
Line I want to Keep


I've recorded a macro, however when I go to delete the lines it deletes the same row numbers everytime. I go through about 20 of these sheets a day, and the data for each part can be from 17-19 rows long, and there can be up to 200ish groups of the 17-19 lines of data on each sheet. So that didn't work out to well for me . Any help would be appreciated. Thanks!

 

[Smitty]

Welcome to the Board!

See if this gets you started:

<font face=Tahoma><SPAN style="color:#00007F">Sub</SPAN> foo()<br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>        <br>        <SPAN style="color:#00007F">For</SPAN> i = Cells(Rows.Count, "A").End(xlUp).Row <SPAN style="color:#00007F">To</SPAN> 1 <SPAN style="color:#00007F">Step</SPAN> -1<br>            <SPAN style="color:#00007F">If</SPAN> InStr(Cells(i, "A").Text, "Pc.") <SPAN style="color:#00007F">Or</SPAN> InStr(Cells(i, "A").Text, "Total") <SPAN style="color:#00007F">Or</SPAN> InStr(Cells(i, "A").Text, "Delivery") <SPAN style="color:#00007F">Then</SPAN><br>                Cells(i, "A").EntireRow.Delete<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> i<br>            <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Hope that helps,

 

[tribalbydesign]

Welcome to the Board!

See if this gets you started:

Sub foo()
Dim i As Long

For i = Cells(Rows.Count, "A").End(xlUp).Row To 1 Step -1
If InStr(Cells(i, "A").Text, "Pc.") Or InStr(Cells(i, "A").Text, "Total") Or InStr(Cells(i, "A").Text, "Delivery") Then
Cells(i, "A").EntireRow.Delete
End If
Next i

End Sub

Hope that helps,

Thanks! I got the macro to delete the lines I was looking for (there were about 10 more than that). I added my original code for the headings and the inserts and formatting and everything works great!