PDA

View Full Version : How can I write a loop to clear cells evenly spaced apart?



sois
08-05-2011, 01:19 PM
Hi guys, I'm getting back into VBA again and I have a task. I need to clear cells that are evenly distance apart. In my eample code, they are 4 columns apart. Basically, I would like a loop to do this for 200 columns or so.

Here is the manual way I am doing it now. Thanks!


Range("A4:A20").Select
Selection.ClearContents
Range("E4:E20").Select
Selection.ClearContents
Range("I4:I20").Select
Selection.ClearContents
Range("M4:M20").Select
Selection.ClearContents

GTO
08-05-2011, 03:44 PM
Not well though through, but maybe something like:
Option Explicit

Sub exa()
BuilRange(1, 200, 4, 4, 20, Sheet1).ClearContents
End Sub

Function BuilRange(FirstCol As Long, _
LastCol As Long, _
Interval As Long, _
FirstRow As Long, _
LastRow As Long, _
wks As Worksheet _
) As Range
Dim i As Long
Dim rng As Range

Set rng = Range(wks.Cells(FirstRow, FirstCol), wks.Cells(LastRow, FirstCol))

For i = FirstCol + Interval To LastCol Step Interval
Set rng = Application.Union(rng, Range(wks.Cells(FirstRow, i), wks.Cells(LastRow, i)))
Debug.Print rng.Address
Next

Set BuilRange = rng
End Function
Hope that helps,

Mark

Aflatoon
08-08-2011, 04:21 AM
You could also use a simple loop:

Dim lngCol As Long
For lngCol = 1 To 200 Step 4
Range(Cells(4, lngCol), Cells(20, lngCol)).ClearContents
Next lngCol