VBA clear contents multiple ranges

[inkserious]

I have a worksheet that has multiple ranges that have to be cleared. The ranges are non consecutive and currently have to be selected by hand and be cleared. They have a pattern: every third column and every 4th and 5th row need to be skipped. So I need to begin at cell F5 and select F5:G7 and ClearContents. And then skip column H, and then select I5:J7 and ClearContents. This needs to be completed through the range CR5:CS7.

I then need to start over at row 10 and begin the same process with the last ranges beginning at F215:G217 and ending at CR215:CS217.

To complicate matters, every 5th row must be completely cleared beginning with row 9. So F9:CT9, and then F14:CT14 all the way through F219:CT219

I recorded a macro just selecting a few of the ranges to get an idea of how it might look, but after looking at the code I know there must be a much more efficient way of doing things.

Any help would be greatly appreciated.

Thanks in advancd. -edward

Sub ClearRanges()

    Range("F9:CT9,F14:CT14").Select
    Range("CT14").Activate
    Selection.ClearContents
    
    Range("F5:G7,I5:J7,L5:M7,F10:G12,I10:J12,L10:M12,F15:G17,I15:J17,L15:M17"). _
        Select
    Range("L15").Activate
    Selection.ClearContents
End Sub

 

[Gruuuu]

If your pattern never changes, there's really nothing wrong with this. If your pattern does change, it's easier to change the reference of the cells rather than using logical references.

You can make it a little better though, by removing the Select method, and applying the ClearContents method directly to your range reference, life so:

Range("F5:G7,I5:J7,L5:M7,F9:CT9,F10:G12,I10:J12,L10:M12,F14:CT14,F15:G17,I15:J17,L15:M17").ClearContents

Or, if it makes it easier, you can define a Named Range. In the Insert->Name->Define

type a new name in the box up top, click the red button near the bottom, make your aggregated selection, hit the red button again, and click add.

If you need to change it, you can go through the exact same process, except you select the name from the list instead of typing a new name.

Then, in your macro, you would use

Range("[i]thenameyouchose[/i]").ClearContents

 

[inkserious]

I tried the idea of naming the range(s), but Excel has limitations on how many non consecutive cells it will allow you to name at one time (not sure what the limit is). So I would have to have numerous named ranges. In this case; however, my pattern will never change.

Thanks for your help.

-edward

 

[PHV]

A starting point:

For r = 5 To 215 Step 10 '5,15,..,215
  For c = 6 To 96 Step 3 'F,I,..,CR
    Range(Cells(r, c), Cells(r + 2, c + 1)).ClearContents
  Next
  Range(Cells(r + 4, 6), Cells(r + 4, 98)).ClearContents
Next

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

hi,

Sub ClearRanges()

    Union([F9:CT9],[F14:CT14]).ClearContents
   
    Union([F5:G7],[I5:J7],[L5:M7],[F10:G12],[I10:J12],[L10:M12],[F15:G17],[I15:J17],[L15:M17]).ClearContents
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[DaveInIowa]

Hey Skip - I was heading down the Union path also and thought I'd return the entire work area in a function in case other methods needed to be applied to the range.

Public Sub ClearRanges()
    Dim rgWorkArea As Range
    Set rgWorkArea = GetWorkArea
    rgWorkArea.Select '<- For testing only -- Don't put this in production code
    rgWorkArea.ClearContents
End Sub

Public Function GetWorkArea(Optional rows As Integer = 43, Optional cols As Integer = 31) As Range
    Dim ix As Long
    Dim rg As Range
    
    Set GetWorkArea = Range("F9:CT9")
    
    Set rg = Range("F5:G7")
    For ix = 1 To cols
        Set GetWorkArea = Application.Union(GetWorkArea, rg)
        Set rg = rg.Offset(0, 3)
    Next
    
    Set rg = GetWorkArea
    For ix = 1 To rows
        Set GetWorkArea = Application.Union(GetWorkArea, rg)
        Set rg = rg.Offset(5, 0)
    Next
    
End Function

 

[inkserious]

All,

Thanks for all of the responses.

DaveInIowa, your solution seems to be going down the right path as it selects all the rows that need to be cleared, F9:CT9, F14:CR14, F19:CT19, etc. However, it is only selecting the first set of ranges that need to be cleared - F5:G7, I5:J7, L5:M7, etc.

It does not go down the sheet and select the subsequent ranges - F10:G12, I10:J12, L10:M12, etc. all the way down to F215:G217, I215:G217, etc.

Any ideas?

Thanks again.

 

[DaveInIowa]

It works for me. Are you sure you typed/copied it in correctly? The For ix = 1 To rows code block is where it goes down the worksheet and adds the row blocks to the range returned.

 

[inkserious]

I copied and pasted it directly from your post. It wasn't working correctly, so I uncommented rgWorkArea.Select and commented 'rgWorkArea.ClearContents to see which cells it was selecting, and that is where I noticed it was not selecting blocks below row 7. It is selecting the entire rows below row 7: 9, 14, 19, etc.

So F5:G7 all the way to CR5:CS7 are being selected; however, F10:G12, F15:G17 all the way through F215:G217 are not. I hope that makes sense.

Not sure what I'm missing here.

 

[DaveInIowa]

I understand what you're saying but it's selecting the correct areas for me. I'm using Excel 2007 but I can't believe that would make any difference.

Are you sure you are initializing rg with Set rg = Range("F5:G7") before performing the Union across columns?

 

[inkserious]

I'm at home now using Office for Mac. I didn't get a chance to try your post before leaving my PC at work. I don't know if that makes any difference or not, but I'll try it when I get to work in the morning using Excel 2003 and see what happens.

I'll re-post and let you know the outcome.

Thanks again.

-edward

 

[PHV]

edward, just to know, did you try my suggestion ?

 

[inkserious]

PH,

Yes, I did try your suggestion. I'm not familiar with loops, so I wasn't sure if it was working or not as it seemed to calculate for a very long time.

I got caught up in the other posts, but I have since gone back and tried it again. I tried shrinking the ranges so that I could test it. I found it to work properly, albeit very slow.

I'm at home using Office for Mac - which may be the culprit behind the speed. I'll test it first thing in the morning at work on a PC.

I'll post again and let you know the outcome.

Thanks.

-edward

 

[PHV]

As for speed issue:

Application.ScreenUpdating = False
' the loop here
Application.ScreenUpdating = True

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[inkserious]

DaveInIowa,

When I tried the macro at work on my PC it ran beautifully - and almost instantaneously. I guess Office for Mac didn't like the code.

PH,

I added the ScreenUpdating code, and I was able to get the cell contents to clear; however, the loop still took a few minutes to complete.

Thanks to everyone for their help.

-edward