loop macro that deletes cells where value = reference cell

[dpolson]

I need to create some macros that deletes the cell contents of cells in a column that:

[a] equal
[b] do not equal
[c] are greater than
[d] are less than
[e] are between
[f] are not between
the value(s) in one (two) other reference cell(s).

I've tried to do it the "record macro" way, but I haven't been able to get the recorded macro set up to start from any other cell than the one I started in when I recorded it.

I found the below macro code from a different post that deletes entire rows where there are consecutive duplicate values:

Sub delete_duplicate_rows()
rowx = 1
Do Until Cells(rowx + 1, 1).Value = ""
If UCase(Cells(rowx, 1).Value) = UCase(Cells(rowx + 1, 1).Value) Then
Cells(rowx + 1, 1).EntireRow.Delete
Else
rowx = rowx + 1
End If
Loop
End Sub

Can this code be modified to do what I'm looking for? Or does it require something different altogether?

Also, does anyone have a suggestion of where to look for a good VBA-macro writing education/training info? I'd like to move beyond the newborn "record macro" stage.

Thanks for the help...

Dale

[Iain Lewis]

With out looking at the data it'd be pretty difficult ( for me ) to advise what is the best way. If you want to send me a copy of the file & some info about waht you're trying to achieve then I'll have a go at knocking something up.

If you want to learn more about writing macros then I'd recommend http://peach.ease.lsoft.com/archives/excel-g.html
this is an email help group with some fantastic help available. It's aimed at all levels of users with the emphasis on on everyone learning from each other.

[dpolson]

OK, here's an example:

In column A is the case number, in B is the value, in C is the POS/neg (0/1) interpretation, and in D is the "copy, paste special, values" of C.
The equation in C is:

=IF(B1="","",IF(B1<0.4,0,1))

In columns ABCD are the below data (remember, column C is the above equation and column D is the "copy-paste special-values" from C).

1 0.0117 0 0
2 -0.0322 0 0
3 2.6209 1 1
4 2.6154 1 1
5 0.4346 1 1
6 0 0 0
7 0.0058 0 0
8 0.5621 1 1
9 0.9669 1 1
10 0.017 0 0
11 0.2445 0 0
12 0.0497 0 0
13 2.0841 1 1
14 0.203 0 0
15 0.7908 1 1
16 0.1667 0 0
17 -0.0497 0 0
18 1.4326 1 1
19 0.9499 1 1
20 0.3398 0 0
21
22 0.0283 0 0
23 -0.3092 0 0
24 2.4148 1 1
25 1.9118 1 1
26 2.4769 1 1
27 0.0261 0 0
28 0.0401 0 0
29 0.0752 0 0
30 0.8747 1 1
31 0.2843 0 0
32 -0.0396 0 0
33 -0.0117 0 0
34 0.0283 0 0
35 2.8764 1 1
36 0.0453 0 0
37 1.761 1 1
38 0.2089 0 0
39 0.2897 0 0
40 0.0594 0 0
41 0.3158 0 0
42 0.1554 0 0
43 -0.0057 0 0
44 0.9739 1 1
45 1.7973 1 1
46 0.5213 1 1
47 2.4174 1 1
48 0.7255 1 1
49 0.9695 1 1
50 0.761 1 1
51 0.1337 0 0
52
53 0.0226 0 0
54
55 1.507 1 1
56 1.5852 1 1
57 2.7304 1 1
58 0.8092 1 1
59 1.7608 1 1
60 1.3464 1 1
61 -0.0622 0 0
62 0.8324 1 1
63 -0.0141 0 0
64 0.7964 1 1
65 0.2582 0 0
66 1.8132 1 1
67 1.5496 1 1
68 -0.038 0 0
69 0.0632 0 0
70 0.2882 0 0
71 0.8824 1 1
72 0.2316 0 0
73 1.1978 1 1
74 0.0263 0 0
75 0.0057 0 0
76 0.2112 0 0
77 1.5043 1 1
78 0.3434 0 0
79 1.1699 1 1
80 0.8333 1 1
81 2.2468 1 1
82 0.1047 0 0
83 0.2306 0 0
84 0.0736 0 0
85 0.3399 0 0
86 1.3654 1 1
87 0.6566 1 1
88 0.2335 0 0
89
90 0.0205 0 0
91 0.0877 0 0
92
93 2.5934 1 1
94 0.0566 0 0
95 0.2386 0 0
96 0.6972 1 1
97 0.4216 1 1
98 0.1003 0 0
99 0.7201 1 1
100 -0.0029 0 0
101 2.3736 1 1
102 0.0322 0 0
103 -0.0255 0 0
104 -0.0707 0 0
105 -0.0509 0 0
106 2.6051 1 1
107
108 1.7451 1 1
109 0.4485 1 1
110 1.0967 1 1
111 -0.0205 0 0
112 0.0948 0 0
113 0.8728 1 1
114 2.3428 1 1
115 -0.0146 0 0
116 2.3517 1 1
117 0.5523 1 1
118 -0.0424 0 0
119 0.0175 0 0
120 1.402 1 1
121 2.0204 1 1
122 1.7214 1 1
123 0.683 1 1
124 0.6699 1 1
125 -0.0058 0 0
126 -0.0205 0 0
127 0.7386 1 1
128 0.0797 0 0
129 -0.0085 0 0
130 0.1961 0 0
131 0.9281 1 1
132 -0.1608 0 0
133 1.7608 1 1
134 0.1797 0 0
135 2.7527 1 1
136 0.3609 0 0
137 0.3599 0 0
138 0.0057 0 0
139 1.218 1 1
140 1.0967 1 1
141 0.4615 1 1
142 0.017 0 0
143 0.2614 0 0
144 0.193 0 0
145 0.0824 0 0
146 -0.0481 0 0
147 0.6845 1 1
148 1.5238 1 1
149 0.614 1 1
150 2.5 1 1
151 0.2288 0 0
152 1.2061 1 1
153 1.0305 1 1
154 0.8244 1 1
155 0.0906 0 0
156 2.8495 1 1
157 0.5599 1 1
158 0.0679 0 0
159 -0.0201 0 0
160 0.1754 0 0
161 2.2206 1 1
162 0.6111 1 1
163
164 0.3383 0 0
165 1.3562 1 1
166 0.2145 0 0
167 0.6242 1 1
168 -0.038 0 0
169 -0.0368 0 0
170 2.3797 1 1
171 -0.0453 0 0
172 1.4427 1 1
173 0.2647 0 0
174 0.5191 1 1
175 0.1281 0 0
176 -0.0198 0 0
177 -0.0526 0 0
178 1.1018 1 1
179 -0.0146 0 0
180 -0.0351 0 0
181 -0.0146 0 0
182 0.0306 0 0
183 0.0677 0 0
184 0.2381 0 0
185 0.25 0 0
186 1.112 1 1
187 2.5064 1 1
188 0.7068 1 1
189 0.1328 0 0
190 0.1078 0 0
191 0.2682 0 0
192 0.9248 1 1
193 0.0819 0 0
194 0.2941 0 0
195 2.2832 1 1
196 0.549 1 1
197 0.116 0 0
198 0.0339 0 0
199 2.1501 1 1
200 0.0947 0 0
201 0.3944 0 0
202 0.6463 1 1
203 1.7536 1 1
204 0 0 0
205 0.4336 1 1
206 2.2772 1 1
207 0.369 0 0
208 -0.0614 0 0
209 -0.0113 0 0
210 0.1016 0 0
211 0.8117 1 1
212 -0.0234 0 0
213 -0.038 0 0
214 -0.0117 0 0
215 0.0817 0 0
216 1.6621 1 1
217 0.8856 1 1
218 1.3995 1 1
219 0.2417 0 0
220 1.0276 1 1
221 0.0234 0 0
222 0.4423 1 1
223 -0.0058 0 0
224 0.3033 0 0
225 1.5293 1 1
226 0.6896 1 1
227 2.0738 1 1
228 0.5237 1 1
229 -0.0085 0 0
230 1.772 1 1
231
232
233 0.2088 0 0
234 0 0 0
235 0.0526 0 0
236 0.634 1 1
237 1.6967 1 1
238 0.0497 0 0
239 2.7303 1 1
240 0.6236 1 1
241 0.5288 1 1
242 0.1275 0 0
243 0.0198 0 0
244 -0.0058 0 0
245 1.989 1 1
246 0.183 0 0
247 0.0028 0 0
248 0.1671 0 0
249 0.8117 1 1
250 0.0251 0 0
251 0.4444 1 1
252 0.2506 0 0
253 0.2431 0 0
254 0.0752 0 0
255 1.7684 1 1
256 0.2353 0 0
257 3.1621 1 1
258 0.2431 0 0
259 0.0451 0 0
260 0.6768 1 1
261 0.0339 0 0
262 -0.0789 0 0
263 0.0451 0 0

What I need is a macro that I can start from anywhere in column C; that, from that relative point, goes on down the remaining cells in columns C, does a "copy-paste special-values" to column D (from C) and checks each cell in column D (depending on the criteria specified in another cell - in this case, I'd want it to find all blank cells, but may also want the option of having it instead find and delete all the cells with a 1 or zero) and then delete the contents of those blank cells...and it needs to loop through the column until it hits the bottom.

If you are wondering why I'd like it to delete the contents of an already blank cell, here's why: I'm trying to figure out a way to deal with the graphing problem in an automated way described at the following previous post...

http://www.mrexcel.com/board/viewtop...c=5082&forum=2

Thats one example. I can provide other examples for the other macros needed in the original post.

When I used to use Lotus 1-2-3, I could write a set of Lotus macros all tied together by a menu bar to do this (and all the things described in the original post). But since I converted to Excel, I'm trying to be able to do these same things in Excel with VBA that I was able to do in Lotus. I'm guessing that most if not all things I used to be able to do in Lotus can be done in Excel and more many times over, so I'm looking for some help to tap into these capabilities in Excel.

Any help is appreciated.

Dale