Let's say I start an Excel spreadsheet and I have data in A1, and A2, and
A3... now let's say I need to insert something between A2 and A3, so when I
do, A4 will have the A3 data... (follow me?). My question is, how do I get A3
to move automatically to B1 instead.

In other words, if I have an objective to keep data to only 3-column wide
spreadsheet, how do I insert data without Excel moving other data to the 4th
column? How do I make the data automatically move down instead of over when
it hits a boundary?

Visually, this is what I want:

A B C
D E F
G H I

and when I right click, and insert something (Bw) between cell-B and cell-C,
I want C to go down AUTOMATICALLY, instead of over automatically, so it goes:

A B Bw
C D E
F G H
I

and NOT:

A B Bw C
D E F
G H I

Thanks folks, I hope my question is understandable.

Insert this event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value = "" Then Exit Sub
m = Cells(Rows.Count, 1).End(xlUp).Row
Set r = Range("A1:D" & m)
If Intersect(Target, r) Is Nothing Then Exit Sub
Dim s()
n = Application.WorksheetFunction.CountA(r)
ReDim s(n + 3)
i = 0
Application.EnableEvents = False
For Each rr In r
If rr.Value < "" Then
s(i) = rr.Value
i = i + 1
End If
Next
r.Clear
i = 0
For k = 1 To m + 1
For j = 1 To 3
If s(i) = "" Then GoTo finish
Cells(k, j).Value = s(i)
i = i + 1
Next
Next
finish:
Application.EnableEvents = True
End Sub


Because it is worksheet code, it is very easy to install and use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm



--
Gary''s Student - gsnu200836


"SEKKDS" wrote:

Let's say I start an Excel spreadsheet and I have data in A1, and A2, and
A3... now let's say I need to insert something between A2 and A3, so when I
do, A4 will have the A3 data... (follow me?). My question is, how do I get A3
to move automatically to B1 instead.

In other words, if I have an objective to keep data to only 3-column wide
spreadsheet, how do I insert data without Excel moving other data to the 4th
column? How do I make the data automatically move down instead of over when
it hits a boundary?

Visually, this is what I want:

A B C
D E F
G H I

and when I right click, and insert something (Bw) between cell-B and cell-C,
I want C to go down AUTOMATICALLY, instead of over automatically, so it goes:

A B Bw
C D E
F G H
I

and NOT:

A B Bw C
D E F
G H I

Thanks folks, I hope my question is understandable.