PDA

View Full Version : Excel-Moving data in a column in a 'circular' way



kikonas
08-11-2009, 02:47 AM
Hello,

I have a column with data.I try to move down my data but the data that will exceed from the initial last cell of my column must be placed in the respective order in the cells on the top of my column that became empty after the move down.Is there any idea how this could be done?
Here is an example:

http://www.freeimagehosting.net/uploads/e3e46c3c61.jpg

(imagine this with hundred of numbers...,it is usefull when for example the left column is time(minutes) and you want to offset in time your incoming values)

Thanks in advance.

odyssey
08-11-2009, 11:19 AM
Welcome to PressF1.

If I understand you correctly, you essentially when you insert a row you want the last value in the column to loop and become the first item in the column. If this is the case then you can do this using VBA. If you go into the visual basic editor (Alt + F11) and then create a new module (from the menu select: Insert->Module) and paste in the following:

Sub RowInsert()

Application.ScreenUpdating = False
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("A1").Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.Cut
Range("A1").Select
ActiveSheet.Paste
Application.ScreenUpdating = True

End Sub

When you run the macro it inserts a row and loops the last cell to the top. Note you may want to assign a shortcut (e.g. Ctl + Shift + I) to run the macro (In Excel 2003 -> Alt + F8 and then select options and enter your shortcut combination). This code is assuming the values are in column A and starting from cell A1, athough obviously you can modify this to suit.

HTH
Dave