Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: loop macro that deletes cells where value = reference cell

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    Board Regular
    Join Date
    Apr 2002
    Location
    Midlands, UK
    Posts
    217
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •