A1 style Range in programming

[Guest]

I would like to know if there is an easier way to accomplish the following:

Currently I am trying to clear blocks of cells across a spreadsheet but not
clear some columns between which have formulas. I am using a looping
structure currently and blanking each cell individually. While this works I
also realize that using a .Range("A1:B25").ClearContents works much much
faster.

Is there an easy conversion to convert a looped number to an A1 style range?

Specifically the loop is currently

For X=9 to 217 Step 4
For Y=11 to 34
Worksheets("Research").Cells(X,Y)=""
Next Y
Next X

Converting those blocks of cells to a range would be much faster.

 

[Guest]

Oops, reversed the X and Y....should be Cells(Y,X)

 

[Harald Staff]

Sub test()
Dim X As Long
With Worksheets("Research")
For X = 9 To 217 Step 4
.Range(.Cells(X, 11), .Cells(X, 34)).ClearContents
Next X
End With
End Sub


HTH. Best wishes Harald

 

[Dianne Butterworth]

If the cells you want to clear contain constants, you could try this:


Worksheets("Research").Range("K9:AH217").SpecialCells(xlCellTypeConstants).C
learContents

This will leave your formulas intact.