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.

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

" wrote:

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.

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

" skrev i
melding ...
Oops, reversed the X and Y....should be Cells(Y,X)

" wrote:

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.

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


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

This will leave your formulas intact.

--
Dianne Butterworth


wrote:
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.