Code to delete rows based on value

[dfbriles]

I have the following code that will delete all rows that DO NOT contai
the value "11327" in column A. I need to expand this code to includ
roughly 40 values.

Here is what I have:

Sub delete_It()

Dim MyRange1 As Range
Dim MyRange As Range
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A1:A" & lastrow)
For Each c In MyRange
If InStr(1, UCase(c.Value), "11327", 0) Then
If MyRange1 Is Nothing Then
Set MyRange1 = c.EntireRow
Else
Set MyRange1 = Union(MyRange1, c.EntireRow)
End If
End If
Next
If Not MyRange1 Is Nothing Then
MyRange1.Delete
End If

End Sub

Any help will be greatly appreciated.

Thanks,
DF

 

[GS]

one way, perhaps...

Sub Delete_SelectiveRows()
Dim vCriteria, vData, v, s1$, n&, j&
vCriteria = Split(Range("A1").Value, ",")
vData = Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
For n = UBound(vData) To LBound(vData) Step -1
For j = LBound(vCriteria) To UBound(vCriteria)
If InStr(1, vData(n, 1), vCriteria(j)) > 0 Then
If Not InStr(1, s1, "0" & n + 1) > 0 Then
s1 = s1 & "," & n + 1
End If
End If
Next 'j
Next 'n
For Each v In Split(Mid(s1, 2), ",")
Rows(v).Delete
Next 'v
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[dfbriles]

;1613973']one way, perhaps...

Sub Delete_SelectiveRows()
Dim vCriteria, vData, v, s1$, n&, j&
vCriteria = Split(Range("A1").Value, ",")
vData = Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
For n = UBound(vData) To LBound(vData) Step -1
For j = LBound(vCriteria) To UBound(vCriteria)
If InStr(1, vData(n, 1), vCriteria(j)) > 0 Then
If Not InStr(1, s1, "0" & n + 1) > 0 Then
s1 = s1 & "," & n + 1
End If
End If
Next 'j
Next 'n
For Each v In Split(Mid(s1, 2), ",")
Rows(v).Delete
Next 'v
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Garry,

I failed to mention in my post that I am almost completely unfamilia
with VBA in Excel. That code I had was Frankenstein-ed together fro
bits of code I came across. Therefore, I do not know how to insert m
parameters into your code. Thanks for your help

 

[GS]

;1613973']one way, perhaps...

Sub Delete_SelectiveRows()
Dim vCriteria, vData, v, s1$, n&, j&
vCriteria = Split(Range("A1").Value, ",")
vData = Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
For n = UBound(vData) To LBound(vData) Step -1
For j = LBound(vCriteria) To UBound(vCriteria)
If InStr(1, vData(n, 1), vCriteria(j)) > 0 Then
If Not InStr(1, s1, "0" & n + 1) > 0 Then
s1 = s1 & "," & n + 1
End If
End If
Next 'j
Next 'n
For Each v In Split(Mid(s1, 2), ",")
Rows(v).Delete
Next 'v
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Garry,

I failed to mention in my post that I am almost completely unfamiliar
with VBA in Excel. That code I had was Frankenstein-ed together from
bits of code I came across. Therefore, I do not know how to insert my
parameters into your code. Thanks for your help.

I see!
Put your *roughly 40 values* in A1 as a comma delimited list like
this...

11327,11328,11329...

so your list in colA starts in A2. You can modify this layout to suit
your data layout so long as you edit the code to reflect where the
values are stored and where the list begins in colA. (You can store the
values anywhere but it should be in a row *above* where the list
starts.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[dfbriles]

;1613976']> 'GS[_2_ Wrote: --

;1613973']one way, perhaps...

Sub Delete_SelectiveRows()
Dim vCriteria, vData, v, s1$, n&, j&
vCriteria = Split(Range("A1").Value, ",")
vData = Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
For n = UBound(vData) To LBound(vData) Step -1
For j = LBound(vCriteria) To UBound(vCriteria)
If InStr(1, vData(n, 1), vCriteria(j)) > 0 Then
If Not InStr(1, s1, "0" & n + 1) > 0 Then
s1 = s1 & "," & n + 1
End If
End If
Next 'j
Next 'n
For Each v In Split(Mid(s1, 2), ",")
Rows(v).Delete
Next 'v
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion-

Garry,

I failed to mention in my post that I am almost completely unfamiliar
with VBA in Excel. That code I had was Frankenstein-ed together from
bits of code I came across. Therefore, I do not know how to insert my
parameters into your code. Thanks for your help.-

I see!
Put your *roughly 40 values* in A1 as a comma delimited list like
this...

11327,11328,11329...

so your list in colA starts in A2. You can modify this layout to suit
your data layout so long as you edit the code to reflect where the
values are stored and where the list begins in colA. (You can store th

values anywhere but it should be in a row *above* where the list
starts.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

I entered all 46 of my values into A1 then ran the macro using the cod
you provided. But nothing happened. Should I enter my values in the th
3rd line of script vCriteria = Split(Range("A1").Value, ",")

 

[GS]

I entered all 46 of my values into A1 then ran the macro using the

code
you provided. But nothing happened. Should I enter my values in the
the
3rd line of script vCriteria = Split(Range("A1").Value, ",")?

No, leave the list of values in A1. You may need to format A1 as 'Text'
so Excel doesn;t convert your list to scientific notation if the values
are all numeric.

The code reads your values into vCriteria, then starts making a list of
rows that match, starting from the last row of data in colA and going
up to A2. Then it deletes the rows one by one.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[dfbriles]

I've tried that code multiple times to no avail. I've made attempts a
editing it, but it never does anything. When I run the macro nothin
happens. I have attempted using a new code:

Sub Test()
Dim List As Variant
Dim LR As Long
Dim r As Long
List = Array("2514", "2515", "2516", "2972", "2975", "3044", "3045"
"10710", "10711", "10713", "10714", "10715", "10716", "10723", "10724"
"10725", "10726", "11166", "11171", "11172", "11178", "11301", "11302"
"11307", "11313", "11314", "11322", "11323", "11327", "11328", "111360"
"11498", "11701", "11702", "11703", "11704", "11705", "11706", "11707"
"11708", "12153", "12158", "12164", "12165", "12167", "12168", "12173")
LR = Range("A" & Rows.Count).End(xlUp).Row
For r = LR To 1 Step -1
If IsError(Application.Match(Range("A" & r).Value, List, False)
Then
Rows(r).Delete
End If
Next r
End Sub

But this deletes everything on the worksheet. I just simply canno
understand why I cannot get this to work. I've also had several mor
experienced excel users help me in person. If anyone has any ideas
please let me know.

;1614006']> I entered all 46 of my values into A1 then ran the macr
using the -

code
you provided. But nothing happened. Should I enter my values in the
the
3rd line of script vCriteria = Split(Range("A1").Value, ",")?-

No, leave the list of values in A1. You may need to format A1 as 'Text

so Excel doesn;t convert your list to scientific notation if the value

are all numeric.

The code reads your values into vCriteria, then starts making a list o

rows that match, starting from the last row of data in colA and going
up to A2. Then it deletes the rows one by one.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussio

 

[GS]

I tested again with your sample data in colA and my code worked same as
it did 1st time. Not sure why this isn't just a copy/paste issue for
you! You are putting the code in a standard module, right? You also
understand that when deleting rows you *must always* start at the
bottom row and work up, right?

Perhaps you can post a download link where I can look at your file and
see what details you're not telling us!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[dfbriles]

Below is part of the worksheet in question. The numbers at the top ar
the values I need to keep. They are in column a, row 1. What follows i
the sales report. After all the header gibberish is the sales info. T
the far left under item # is the info that is in column a (i.e. "7
Cookie", "81 Cereal w/ milk", etc.) Thus, the item number and name ar
in the same column. When I run the macro using the code you provide
nothing happens. Not sure what I'm doing wrong.


12173, 12168, 12167, 12165, 12164, 12158, 12153, 11708, 11707, 11706
11705, 11704, 11703, 11702, 11701, 11498, 11360, 11328, 11327, 11323
11322, 11314, 11313, 11307, 11302, 11301, 11178, 11172, 11166, 10726
10725, 10724, 10723, 10716, 10715, 10714, 10713, 10711, 10710, 3045
3044, 2975, 2972, 2516, 2515, 2514
<html>
<body BGCOLOR=ffffee>
<pre>
<font face="Courier,New" s ize=1>
-------------------------- ------- --------- --------------------- ----------- --- --- --- ---------------------------------------------------
Menu Item Sales <i>Univers ity of Ka nsas</i>
1028 Briles
The Studio All <font color=0000ff> SEP20'13
4:52PM</font>
-------------------------- ------- --------- --------------------- ----------- --- --- --- ---------------------------------------------------
Examine <font color=0000f f>SEP19'13 2:00 00A M - SEP20'13
2:00AM</font>


Item # Item Name #Sold Net Sales %of Sales
--------
---------------- ------ --------- -------------------- ----------
71 Cookie 2 1.8 0.03%
81 Cereal w/ Milk 1 2.45 0.04%
93 12oz Water 1 0.99 0.02%
1102 Med Fountain 27 42.93 0.72%
1103 Lg Fountain 5 9.45 0.16%
1105 0.25 Water 8 2 0.03%
1206 Rck Chlk LatteSm 2 7 0.12%
1209 Rck Chlk LatteMd 3 12.6 0.21%
1216 Frappe 8 32 0.53%
1218 MdShot intheDark 1 3.5 0.06%
1220 Sm Roasterie 2 3.4 0.06%
1221 Md Roasterie 6 12 0.20%
1222 Lg Roasterie 1 2.1 0.04%
1228 SmPulseHC 2 5.6 0.09%
1229 MdPulseHC 4 13 0.22%
1237 ADD Large 6 2.7 0.05%
1243 Hot Tea 6 10.5 0.18%
1257 White Mocha Sm 1 3.5 0.06%
1258 White Mocha Md 1 4.2 0.07%
1267 Iced Latte 2 7.2 0.12%
1269 Iced Chai 3 10.5 0.18%
1272 Cold Press Coffe 1 2 0.03%
1278 Iced White Mocha 1 4.2 0.07%
1279 Iced CarmamelMac 2 8.4 0.14%
1297 Mexican Coke 31 31 0.52%
1299 Gold Peak Tea 1 2.2 0.04%
1314 PA Strwbry Lemon 1 2.2 0.04%
1329 12oz Cherry Zero 2 1.8 0.03%
1343 Vita Wtr XXX 1 2.2 0.04%
1346 V8 Spl Tropical 2 4.2 0.07%
1360 NOS Grape 1 2.5 0.04%
1364 GP Tea Swt Green 1 2.2 0.04%
1367 GP Tea Sweetened 5 11 0.18%
1379 WATER 16 0 0.00%
1395 12oz Fanta Grape 1 0.9 0.02%
1399 Coke 12 Pack 3 5.97 0.10

 

[GS]

12173, 12168, 12167, 12165, 12164, 12158, 12153, 11708, 11707, 11706,

11705, 11704, 11703, 11702, 11701, 11498, 11360, 11328, 11327, 11323,
11322, 11314, 11313, 11307, 11302, 11301, 11178, 11172, 11166, 10726,
10725, 10724, 10723, 10716, 10715, 10714, 10713, 10711, 10710, 3045,
3044, 2975, 2972, 2516, 2515, 2514

Here's the problem:this is *not* a comma-delimited string! Instead,
you're using ", " as the delimiter and so you need to do 1 of the
following...

a. remove the spaces
b. revise my code sample to include the space

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[dfbriles]

That's one of the first things I tired, and I've done it both ways
Really not trying to make you pull your hair out here. Would it matte
if all the numbers on my list are not always on the report?

;1614181']> 12173, 12168, 12167, 12165, 12164, 12158, 12153, 11708
11707, 11706,-

11705, 11704, 11703, 11702, 11701, 11498, 11360, 11328, 11327, 11323,
11322, 11314, 11313, 11307, 11302, 11301, 11178, 11172, 11166, 10726,
10725, 10724, 10723, 10716, 10715, 10714, 10713, 10711, 10710, 3045,
3044, 2975, 2972, 2516, 2515, 2514-

Here's the problem:this is *not* a comma-delimited string! Instead,
you're using ", " as the delimiter and so you need to do 1 of the
following...

a. remove the spaces
b. revise my code sample to include the space

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussio

 

[GS]

That's one of the first things I tired, and I've done it both ways.

Really not trying to make you pull your hair out here. Would it
matter
if all the numbers on my list are not always on the report?

So sorry - my bad!!! I copy/pasted the wrong procedure...

Sub Delete_SelectiveRows2()
Dim vCriteria, vData, v, s1$, n&
vCriteria = Range("A1").Value
vData = Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
For n = UBound(vData) To LBound(vData) Step -1
If Not InStr(1, vCriteria, vData(n, 1)) > 0 Then
If Not InStr(1, s1, "0" & n + 1) > 0 Then
s1 = s1 & "," & n + 1
End If
End If
Next 'n
For Each v In Split(Mid(s1, 2), ",")
Rows(v).Delete
Next 'v
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion