Execute code on a range of cells, then move down and do again?
 

I have the following code to clear a range of cells. Now I need it to move
down 15 rows and do it again, what do I need to add in to the code?

Range ( _"B9:B21,F9:F21,A20:C21,C19"_ ).Select
Range ("C19") . Activate
Selection.ClearContents

IF Range ("B9" + 15) = 0 THEN
'do again for the cells starting from B9+15
ELSE
End IF
End Sub






TIA

Execute code on a range of cells, then move down and do again?
 

There are other ways to do it, but for the description you gave, this should
cover it.

Sub clrCont()
Dim sh As Worksheet
sh = ActiveSheet
sh.Range("B9:B19").ClearContents
sh.Range("F9:F21").ClearContents
sh.Range("A20:C21").ClearContents
If sh.Range("B24").Value = 0 Then
sh.Range("B24:B35").ClearContents
sh.Range("F24:F37").ClearContents
sh.Range("A38:C39").ClearContents
End If
End Sub

"BABs" wrote:

I have the following code to clear a range of cells. Now I need it to move
down 15 rows and do it again, what do I need to add in to the code?

Range ( _"B9:B21,F9:F21,A20:C21,C19"_ ).Select
Range ("C19") . Activate
Selection.ClearContents

IF Range ("B9" + 15) = 0 THEN
'do again for the cells starting from B9+15
ELSE
End IF
End Sub






TIA

Execute code on a range of cells, then move down and do again?
 

JLGWhiz,
Thx for the reply. I was going to do this, but I actually have a lot more
ranges and possibly hundreds of repetitions of this code.
What I would like to do is something like:

With ActiveSheet

Dim s1 As Integer
Dim s2 As Integer
Dim s3 As Integer
Dim s4 As Integer
Dim s5 As Integer
Dim s6 As Integer
Dim s7 As Integer
Dim s8 As Integer
Dim s9 As Integer
Dim s10 As Integer

s1 = 9
s2 = 21
s3 = 20
s4 = 12
s5 = 15
s6 = 16
s7 = 13
s8 = 14
s9 = 10
s10 = 19

Line1:

.Range("B & s1:B & s2,F & s1:F & s2,Q & s1:Q & s2,A & s3:C & s2,C & s2,D
& s3:E & s3,G & s3:R & s2,G & s4:R & s4,G & s5:P & s6,R & s5,H & s7:I & s8,J
& s8:K & s8,A & s9:E & s9,G & s1,C & s10").Select
.Selection.ClearContents

s1 = s1 + 15
s2 = s2 + 15
s3 = s3 + 15
s4 = s4 + 15
s5 = s5 + 15
s6 = s6 + 15
s7 = s7 + 15
s8 = s8 + 15
s9 = s9 + 15
s10 = s10 + 15

If Range("B & s1") 0 Then
GoTo Line1
Else
End If
End With




But I can't seem to get the syntax/punctuation to work right.
Where am I going wrong?






"JLGWhiz" wrote:

There are other ways to do it, but for the description you gave, this should
cover it.

Sub clrCont()
Dim sh As Worksheet
sh = ActiveSheet
sh.Range("B9:B19").ClearContents
sh.Range("F9:F21").ClearContents
sh.Range("A20:C21").ClearContents
If sh.Range("B24").Value = 0 Then
sh.Range("B24:B35").ClearContents
sh.Range("F24:F37").ClearContents
sh.Range("A38:C39").ClearContents
End If
End Sub

"BABs" wrote:

I have the following code to clear a range of cells. Now I need it to move
down 15 rows and do it again, what do I need to add in to the code?

Range ( _"B9:B21,F9:F21,A20:C21,C19"_ ).Select
Range ("C19") . Activate
Selection.ClearContents

IF Range ("B9" + 15) = 0 THEN
'do again for the cells starting from B9+15
ELSE
End IF
End Sub






TIA

Execute code on a range of cells, then move down and do again?
 

Found another solution:

Dim sh As Worksheet
Set sh = ActiveSheet

Dim rng1 As Range
Set rng1 = sh.Range("B9")

Dim rng As Range
Set rng =
sh.Range("b9:b21,f9:f21,q9:q21,a20:c21,d20:e20,g20 :r21,g12:r12,g15:p16,r15,h13:i14,j14:k14,a10:e10,g 9,c19")
Line1:
rng.ClearContents

Set rng = rng.Offset(15, 0)
Set rng1 = rng1.Offset(15, 0)

If rng1 = "0" Then
GoTo Line1
Else
End If


too easy!
Thx your message got me thinking which led to this.
learning is fun.............
















"JLGWhiz" wrote:

There are other ways to do it, but for the description you gave, this should
cover it.

Sub clrCont()
Dim sh As Worksheet
sh = ActiveSheet
sh.Range("B9:B19").ClearContents
sh.Range("F9:F21").ClearContents
sh.Range("A20:C21").ClearContents
If sh.Range("B24").Value = 0 Then
sh.Range("B24:B35").ClearContents
sh.Range("F24:F37").ClearContents
sh.Range("A38:C39").ClearContents
End If
End Sub

"BABs" wrote:

I have the following code to clear a range of cells. Now I need it to move
down 15 rows and do it again, what do I need to add in to the code?

Range ( _"B9:B21,F9:F21,A20:C21,C19"_ ).Select
Range ("C19") . Activate
Selection.ClearContents

IF Range ("B9" + 15) = 0 THEN
'do again for the cells starting from B9+15
ELSE
End IF
End Sub






TIA