| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Code to delete rows based on value
I have the following code that will delete all rows that DO NOT contain the value "11327" in column A. I need to expand this code to include 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, DFB |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Code to delete rows based on value
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 |
|
#3
|
|||
|
|||
|
Quote:
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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Code to delete rows based on value
'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 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 |
|
#5
|
|||
|
|||
|
Quote:
|
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Code to delete rows based on value
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 |
|
#7
|
|||
|
|||
|
I've tried that code multiple times to no avail. I've made attempts at editing it, but it never does anything. When I run the macro nothing 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 cannot understand why I cannot get this to work. I've also had several more experienced excel users help me in person. If anyone has any ideas, please let me know. Quote:
|
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Code to delete rows based on value
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 |
|
#9
|
|||
|
|||
|
Below is part of the worksheet in question. The numbers at the top are the values I need to keep. They are in column a, row 1. What follows is the sales report. After all the header gibberish is the sales info. To the far left under item # is the info that is in column a (i.e. "71 Cookie", "81 Cereal w/ milk", etc.) Thus, the item number and name are in the same column. When I run the macro using the code you provided 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 <iUnivers ity of Ka nsas</i 1028 Briles The Studio All <font color=0000ff SEP20'13 4:52PM</font -------------------------- ------- --------- --------------------- ----------- --- --- --- --------------------------------------------------- Examine <font color=0000f fSEP19'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% |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Code to delete rows based on value
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 |
|
#11
|
|||
|
|||
|
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?
Quote:
|
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Code to delete rows based on value
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Delete Rows based on # of rows (no variable?) | Excel Programming | |||
| Delete Rows based on value | Excel Worksheet Functions | |||
| Code for Excel 2007 to hide rows based on sum of several rows not ina range | Excel Programming | |||
| Code help, delete rows based on column criteria | Excel Discussion (Misc queries) | |||
| Delete rows based on value... | Excel Programming | |||
Linear Mode
