Clearing selected Range

[microhunt]

I am using the code below to clear cells when I run Macro. The problem my sheet is very long for example. I am clearing every 4 cell in column C starting at C5 and finishing at C1653. Is there a quicker way of doing this code?


Sub Clearrange(sRange As String, sWorksheet As String)
Worksheets(sWorksheet).Range(sRange).ClearContents
End Sub
Sub ClearFishSheet(sWorksheet As String)




Clearrange ("C5"), sWorksheet ' Range 1
Clearrange ("C13"), sWorksheet ' Range 2
Clearrange ("C17"), sWorksheet ' Range 3
Clearrange ("C21"), sWorksheet ' Range 4
Clearrange ("C25"), sWorksheet ' Range 5
Clearrange ("C29"), sWorksheet ' Range 6
Clearrange ("C33"), sWorksheet ' Range 7
Clearrange ("C37"), sWorksheet ' Range 8
Clearrange ("C41"), sWorksheet ' Range 9
Clearrange ("C45"), sWorksheet ' Range 10
Clearrange ("C49"), sWorksheet ' Range 11
Clearrange ("C53"), sWorksheet ' Range 12
Clearrange ("C57"), sWorksheet ' Range 13
Clearrange ("C61"), sWorksheet ' Range 14
Clearrange ("C65"), sWorksheet ' Range 15
Clearrange ("C69"), sWorksheet ' Range 16
Clearrange ("C73"), sWorksheet ' Range 17
Clearrange ("C77"), sWorksheet ' Range 14
Clearrange ("C81"), sWorksheet ' Range 15
Clearrange ("C85"), sWorksheet ' Range 16
Clearrange ("C89"), sWorksheet ' Range 17
Clearrange ("C93"), sWorksheet ' Range 18
Clearrange ("C97"), sWorksheet ' Range 19
Clearrange ("C101"), sWorksheet ' Range 20

 

[Rick Rothstein]

Re: Clearing selected Range Help Required

You could clear those cells with just a single line of code (albeit a rather long one)...

[table="width: 500"]
[tr]
	[td]Sub ClearFishSheet(sWorksheet As String)
  Sheets(sWorksheet).Range("C5:C1653") = Evaluate("IF(MOD(ROW('" & Sheets(sWorksheet).Name & "'!C5:C1653)-5,4),'" & Sheets(sWorksheet).Name & "'!C5:C1653,"""")")
End Sub[/td]
[/tr]
[/table]

 

[microhunt]

Re: Clearing selected Range Help Required

Thank You, You have saved me hours of work as I have similar projects. Many Thanks

You could clear those cells with just a single line of code (albeit a rather long one)...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub ClearFishSheet(sWorksheet As String)
  Sheets(sWorksheet).Range("C5:C1653") = Evaluate("IF(MOD(ROW('" & Sheets(sWorksheet).Name & "'!C5:C1653)-5,4),'" & Sheets(sWorksheet).Name & "'!C5:C1653,"""")")
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

 

[microhunt]

Re: Clearing selected Range Help Required

I am so grateful, lucky you were online to help me. Your Genius!

 

[microhunt]

Re: Clearing selected Range Help Required

You could clear those cells with just a single line of code (albeit a rather long one)...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub ClearFishSheet(sWorksheet As String)
  Sheets(sWorksheet).Range("C5:C1653") = Evaluate("IF(MOD(ROW('" & Sheets(sWorksheet).Name & "'!C5:C1653)-5,4),'" & Sheets(sWorksheet).Name & "'!C5:C1653,"""")")
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

I have found a problem with this formula I was provided kindly. The code I want to clear is starting at C5 and Finishing at C1653. I am clearing every four cell in this column see

Clearrange ("C5"), sWorksheet ' Range 1[/COLOR]
[COLOR=#333333]Clearrange ("C13"), sWorksheet ' Range 2[/COLOR]
[COLOR=#333333]Clearrange ("C17"), sWorksheet ' Range 3[/COLOR]
[COLOR=#333333]Clearrange ("C21"), sWorksheet ' Range 4[/COLOR]
[COLOR=#333333]Clearrange ("C25"), sWorksheet ' Range 5[/COLOR]
[COLOR=#333333]Clearrange ("C29"), sWorksheet ' Range 6[/COLOR]
[COLOR=#333333]Clearrange ("C33"), sWorksheet ' Range 7[/COLOR]
[COLOR=#333333]Clearrange ("C37"), sWorksheet ' Range 8[/COLOR]
[COLOR=#333333]Clearrange ("C41"), sWorksheet ' Range 9[/COLOR]
[COLOR=#333333]Clearrange ("C45"), sWorksheet ' Range 10[/COLOR]
[COLOR=#333333]Clearrange ("C49"), sWorksheet ' Range 11[/COLOR]
[COLOR=#333333]Clearrange ("C53"), sWorksheet ' Range 12[/COLOR]
[COLOR=#333333]Clearrange ("C57"), sWorksheet ' Range 13[/COLOR]
[COLOR=#333333]Clearrange ("C61"), sWorksheet ' Range 14[/COLOR]
[COLOR=#333333]Clearrange ("C65"), sWorksheet ' Range 15[/COLOR]
[COLOR=#333333]Clearrange ("C69"), sWorksheet ' Range 16[/COLOR]
[COLOR=#333333]Clearrange ("C73"), sWorksheet ' Range 17[/COLOR]
[COLOR=#333333]Clearrange ("C77"), sWorksheet ' Range 14[/COLOR]
[COLOR=#333333]Clearrange ("C81"), sWorksheet ' Range 15[/COLOR]
[COLOR=#333333]Clearrange ("C85"), sWorksheet ' Range 16[/COLOR]
[COLOR=#333333]Clearrange ("C89"), sWorksheet ' Range 17[/COLOR]
[COLOR=#333333]Clearrange ("C93"), sWorksheet ' Range 18[/COLOR]
[COLOR=#333333]Clearrange ("C97"), sWorksheet ' Range 19[/COLOR]
[COLOR=#333333]Clearrange ("C101"), sWorksheet ' Range 20

I have found a problem that the code is also removing formula starting at C7, C11, and every four cell so on up to C1657 which I do not want to clear

 

[microhunt]

Re: Clearing selected Range Help Required

Sorry this is the code I am using that I would like to clear starting at C5, C9, C13 increment every four cell up to C1653. I only want to claer every four cell in this column up to cell C1653

I have found a problem that the code is also removing formula starting at C7, C11, and every four cell so on up to C1657 which I do not want to clear



Sub Clearrange(sRange As String, sWorksheet As String)
Worksheets(sWorksheet).Range(sRange).ClearContents
End Sub
Sub ClearFishSheet(sWorksheet As String)




Clearrange ("C5"), sWorksheet ' Range 1
Clearrange ("C9"), sWorksheet ' Range 2
Clearrange ("C13"), sWorksheet ' Range 3
Clearrange ("C17"), sWorksheet ' Range 4
Clearrange ("C21"), sWorksheet ' Range 5
Clearrange ("C25"), sWorksheet ' Range 6
Clearrange ("C29"), sWorksheet ' Range 7
Clearrange ("C33"), sWorksheet ' Range 8
Clearrange ("C37"), sWorksheet ' Range 9
Clearrange ("C41"), sWorksheet ' Range 10
Clearrange ("C45"), sWorksheet ' Range 11
Clearrange ("C49"), sWorksheet ' Range 12
Clearrange ("C53"), sWorksheet ' Range 13
Clearrange ("C57"), sWorksheet ' Range 14
Clearrange ("C61"), sWorksheet ' Range 15
Clearrange ("C65"), sWorksheet ' Range 16
Clearrange ("C69"), sWorksheet ' Range 17
Clearrange ("C73"), sWorksheet ' Range 18
Clearrange ("C77"), sWorksheet ' Range 19
Clearrange ("C81"), sWorksheet ' Range 20
Clearrange ("C85"), sWorksheet ' Range 21
Clearrange ("C89"), sWorksheet ' Range 22
Clearrange ("C93"), sWorksheet ' Range 23
Clearrange ("C97"), sWorksheet ' Range 24
Clearrange ("C101"), sWorksheet ' Range 25

 

[microhunt]

Re: Clearing selected Range Help Required

Can anyone solve this problem are do you think it is best to type out each line manually?