PDA

View Full Version : Solved: Shifting rows????



twelvety
03-14-2009, 06:47 AM
Hi,

I have a question very similar to the one I asked in the thread http://www.vbaexpress.com/forum/showthread.php?t=25626. I need to shift the cells in my spreadsheet to the right by the following amounts.

First block of 19:
row 1 moves 1 to the right
row 2 moves 2 to the right
etc etc moving one more each time until row 9/10 then moving one less
row 17 moves 2 to the right
row 18 moves 1 to the right

Next block of 18:
row 1 moves 1 to the right
row 2 moves 2 to the right
etc etc moving one more each time until row 9 then moving one less
row 16 moves 2 to the right
row 17 moves 1 to the right

Next block of 17:
row 1 moves 1 to the right
row 2 moves 2 to the right
etc etc moving one more each time until row 8/9 then moving one less
row 15 moves 2 to the right
row 16 moves 1 to the right

Is anyone able to offer some code to do this?

Thank you

mdmackillop
03-14-2009, 07:16 AM
Sub Moving()
j = 0
k = 0
For i = 2 To 10
j = j + 1
Cells(j, i) = Cells(j, 1)
Cells(19 - k, i) = Cells(19 - k, 1)
Next
j = 18
For i = 2 To 10
j = j + 1
k = k + 1
Cells(j, i) = Cells(j, 1)
Cells(37 - k, i) = Cells(37 - k, 1)
Next
j = 36
k = 0
For i = 2 To 10
j = j + 1
k = k + 1
Cells(j, i) = Cells(j, 1)
Cells(54 - k, i) = Cells(54 - k, 1)
Next

End Sub

twelvety
03-14-2009, 08:08 AM
The attachment shows the result I am trying to achieve. Where there are empty cells at the begining of the rows it is because the rows have moved along by this amount. As you move down the spreadsheet they are moving along less and less. Can anyone supply the code to achieve this or a result similar to this?

Thanks

mdmackillop
03-14-2009, 09:36 AM
Apologies, I didn't inspect your original post
Try
Sub MakeWave()

a = 1
r = 20
Do
j = 0
r = r - 1

For i = a To a + Int(r / 2) + 1
j = j + 1
If Cells(i, 1) = "" Then Exit For
Cells(i, 1).Resize(, j).Insert xlToRight
If Cells(a + r - j, 1) = "" Then Exit For
Cells(a + r - j, 1).Resize(, j).Insert xlToRight
Next
a = a + r + 1
Loop Until r = 0


End Sub

mdmackillop
03-14-2009, 10:57 AM
and not a word of thanks in either thread.
(added to "Ignore" list)