cubbycub
10-10-2007, 02:13 AM
Hello All
I'm trying to write a bit of code that will, on the press of a button, offset a range of selected cells to the right, dependent on another cell. If that cell is empty then i would want to offset the cells to the left.
The following code does do this for me but it seems way too clumsy and so so S L O W! :dunno Is there another, slicker way to move the cells?
Sub move()
For Each rngArea In Selection.Areas
For Each rngCode In rngArea.Cells
rngCode.Select
If ActiveCell.Offset(0, 3).Value <> "" Then
For i = 70 To 1 Step -1
ActiveCell.Offset(0, i + 7) = ActiveCell.Offset(0, i)
ActiveCell.Offset(0, i) = ""
Next i
Else
For i = 1 To 70
ActiveCell.Offset(0, i) = ActiveCell.Offset(0, i + 7)
ActiveCell.Offset(0, i + 7) = ""
Next i
End If
Next rngCode
Next rngArea
End Sub
I'm trying to write a bit of code that will, on the press of a button, offset a range of selected cells to the right, dependent on another cell. If that cell is empty then i would want to offset the cells to the left.
The following code does do this for me but it seems way too clumsy and so so S L O W! :dunno Is there another, slicker way to move the cells?
Sub move()
For Each rngArea In Selection.Areas
For Each rngCode In rngArea.Cells
rngCode.Select
If ActiveCell.Offset(0, 3).Value <> "" Then
For i = 70 To 1 Step -1
ActiveCell.Offset(0, i + 7) = ActiveCell.Offset(0, i)
ActiveCell.Offset(0, i) = ""
Next i
Else
For i = 1 To 70
ActiveCell.Offset(0, i) = ActiveCell.Offset(0, i + 7)
ActiveCell.Offset(0, i + 7) = ""
Next i
End If
Next rngCode
Next rngArea
End Sub