PDA

View Full Version : Solved: Is there a faster way to move these cells



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

p45cal
10-10-2007, 08:13 AM
Try this, it should run faster, but note a few differences listed below the code.
Sub move()
Application.ScreenUpdating = False
For Each rngArea In Selection.Areas
For Each rngCode In rngArea.Cells
If rngCode.Offset(0, 3).Value <> "" Then 'move to right seven columns
rngCode.Resize(, 70).Offset(, 1).Cut Destination:=rngCode.Resize(, 70).Offset(, 8)
Else 'move to left seven columns
rngCode.Resize(, 70).Offset(, 8).Cut Destination:=rngCode.Resize(, 70).Offset(, 1)
End If
Next rngCode
Next rngArea
Application.ScreenUpdating = True
End Sub1. Copies values and formats whereas your original code doesn't copy formats. If this is important this can be corrected - post again if so.
2. It leaves the same range(s) selected after the macro has run as were selected before it ran. Yours leaves the last cell processed selected only. This may only be important if more vba code follows.

cubbycub
10-10-2007, 08:44 AM
Thanks P45cal

That works a treat!! :rotlaugh:

I'm quite new to VB and your assistance is very much appreciated

thankyou thnakyou thankyou

lucas
10-10-2007, 08:50 AM
Please mark your thread solved using the thread tools at the top of the page.

cubbycub
10-10-2007, 09:09 AM
Thanks for the remind Steve

lucas
10-10-2007, 09:20 AM
No problem cub, it just helps us not to visit threads that have been resolved...