vba clear contents of specific range in intervals loop

    Hello,


    I am trying to delete specific cells in a pattern/interval. I do not want to use the ranges listed because I want it to loop all the way down the column.



    'Clearcontents of G2:17 (Clear 16 cells)
    'Clearcontents of G81:G88 (63 cells down from last clear, Clear 8 cells)
    'Clearcontents of G152:158 (63 cells down from last clear, Clear 7 cells)
    'Clearcontents of G222:228 (63 cells down from last clear, Clear 7 cells)
    'Clearcontents of G292:298 (63 cells down from last clear, Clear 7 cells)
    'Clearcontents of G364:370 (63 cells down from last clear, Clear 7 cells)
    'Clearcontents of G434:455 (63 cells down from last clear, clear 22 cells)
    'Clearcontents of G519:525 (63 cells down from last clear, Clear 7 cells)
    'Clearcontents of G599:605 (73 cells down from last clear, Clear 7 cells)
    'Loop to top of this for entire column


    I don't have any code yet... any help would be appreciated!


    Thanks!

    Re: vba clear contents of specific range in intervals loop


    I would suggest something like

    Code
    With Sheet1
    .Range("G2:G17").ClearContents
    .Range("G81:G88").ClearContents
    ' etc.
End With


    Loops are good for repeated processes, the variation in interval and range size makes this a poor case for looping.

    Re: vba clear contents of specific range in intervals loop


    Then I would have to call out every range in column G - I am trying to avoid that by using an offset of -63 somehow, then looping up to repeat for the next section of column G all the way down.

    Re: vba clear contents of specific range in intervals loop


    That would be a good idea, but your intervals aren't consitant. Perhaps something like

    Code
    For I = 81 to 599 Step 63
    .Cells(I, "G").Resize(7,1).ClearContents
Next I


    Followed by some clean-up code with explicit addresses to address the out of sequence (e.g. 22cell) ranges.

    Re: vba clear contents of specific range in intervals loop


    Here's what I ended up with using that seems to be working (in case others may benefit)


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login