loop macro that deletes cells where value = reference cell

dpolson

New Member
Joined
Apr 7, 2002
Messages
17
I need to create some macros that deletes the cell contents of cells in a column that:

[a] equal
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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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.
 
Upvote 0
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/viewtopic.php?topic=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
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top