Deleting and shifting cells and columns

Is there any way to shift cells to replace cells that I have deleted.
If I have 3 columns, and I delete an entry in column 1, is there any
way to get the data to shift up 1 cell, and have the cell on top of
column 2, automatically move to the bottom of row 1, and the top cell
on column 3, move to the bottom of column 2.

For examle, 3 columns, and 5 rows
Column A Column B Column C
Row 1) Jim John Frank
Row 2) Ryan Jamal Anne
Row 3) Burt Andy Tom
Row 4) Miguel Kris Paul
Row 5) Todd Missy Dianne

If I delete Burt out of Row A-3, I want Miguel to move up to Row A-3,
Todd to A-4, and John to Column A-5, Jamal to B-1 and Frank to B-5,
Anne to C-1, and so on...

I know you could just delete the Cell A-3, and then drag B-1 to A-5,
and C-1 to B-5, but I'm looking for an easier way.
Thanks for your help.

You could do this with code, and probably the best way to accomplish it would
be to include the delete as part of the code. You'd select the name to be
deleted and then click a button that calls the code (or just choose to run
the macro) and do it that way.

"Curt1521" wrote:

> Is there any way to shift cells to replace cells that I have deleted.
> If I have 3 columns, and I delete an entry in column 1, is there any
> way to get the data to shift up 1 cell, and have the cell on top of
> column 2, automatically move to the bottom of row 1, and the top cell
> on column 3, move to the bottom of column 2.
>
> For examle, 3 columns, and 5 rows
> Column A Column B Column C
> Row 1) Jim John Frank
> Row 2) Ryan Jamal Anne
> Row 3) Burt Andy Tom
> Row 4) Miguel Kris Paul
> Row 5) Todd Missy Dianne
>
> If I delete Burt out of Row A-3, I want Miguel to move up to Row A-3,
> Todd to A-4, and John to Column A-5, Jamal to B-1 and Frank to B-5,
> Anne to C-1, and so on...
>
> I know you could just delete the Cell A-3, and then drag B-1 to A-5,
> and C-1 to B-5, but I'm looking for an easier way.
> Thanks for your help.
>
>

I believe this code will do the trick for you:

Sub MoveNames()
If IsEmpty(ActiveCell) Then
Exit Sub ' nothing to delete
End If
ActiveCell.Delete (xlShiftUp) ' deletes and moves names under it up
Do Until IsEmpty(ActiveCell.Offset(-(ActiveCell.Row - 1), 1))
If Not (IsEmpty(ActiveCell.Offset(-(ActiveCell.Row - 1), 1))) Then
On Error Resume Next
Range(ActiveCell.End(xlDown).Address).Offset(1, 0) =
ActiveCell.Offset(-(ActiveCell.Row - 1), 1).Value
If Err <> 0 Then ' was at bottom of a list
Err.Clear
ActiveCell = ActiveCell.Offset(-(ActiveCell.Row - 1), 1).Value
End If
On Error GoTo 0 ' stop error trapping
Else
Exit Sub ' no names in next column
End If
'move to the top of the next column
ActiveCell.Offset(-(ActiveCell.Row - 1), 1).Select
ActiveCell.Delete (xlShiftUp)
Loop
End Sub



"JLatham" wrote:

> You could do this with code, and probably the best way to accomplish it would
> be to include the delete as part of the code. You'd select the name to be
> deleted and then click a button that calls the code (or just choose to run
> the macro) and do it that way.
>
> "Curt1521" wrote:
>
> > Is there any way to shift cells to replace cells that I have deleted.
> > If I have 3 columns, and I delete an entry in column 1, is there any
> > way to get the data to shift up 1 cell, and have the cell on top of
> > column 2, automatically move to the bottom of row 1, and the top cell
> > on column 3, move to the bottom of column 2.
> >
> > For examle, 3 columns, and 5 rows
> > Column A Column B Column C
> > Row 1) Jim John Frank
> > Row 2) Ryan Jamal Anne
> > Row 3) Burt Andy Tom
> > Row 4) Miguel Kris Paul
> > Row 5) Todd Missy Dianne
> >
> > If I delete Burt out of Row A-3, I want Miguel to move up to Row A-3,
> > Todd to A-4, and John to Column A-5, Jamal to B-1 and Frank to B-5,
> > Anne to C-1, and so on...
> >
> > I know you could just delete the Cell A-3, and then drag B-1 to A-5,
> > and C-1 to B-5, but I'm looking for an easier way.
> > Thanks for your help.
> >
> >

Thank you JLatham. Coding is not my expertise, that's why I came here!

I'll give it a try, and post back. Thanks again!

JLatham,
When I compiled it, I got a syntax error on this line.
Range(ActiveCell.End(xlDown).Address).Offset(1, 0) =

That's a line wrap problem. the line you mention is continued on the next
line. If you want, you can use a line continuation character which is a
spcae followed by an underscore and nothing else on that line ( _)
Range(ActiveCell.End(xlDown).Address).Offset(1, 0) = _
ActiveCell.Offset(-(ActiveCell.Row - 1), 1).Value
otherwise, just bring the 2nd line up to the 1st line making it all one line.
--
Kevin Vaughn


"Curt1521" wrote:

> JLatham,
> When I compiled it, I got a syntax error on this line.
> Range(ActiveCell.End(xlDown).Address).Offset(1, 0) =
>
>

Thanks, Kevin, for clarifying that for Curt - the message editor here tried
to eat my code and you caught it at it! Those 2 lines were all just one long
line originally.

"Kevin Vaughn" wrote:

> That's a line wrap problem. the line you mention is continued on the next
> line. If you want, you can use a line continuation character which is a
> spcae followed by an underscore and nothing else on that line ( _)
> Range(ActiveCell.End(xlDown).Address).Offset(1, 0) = _
> ActiveCell.Offset(-(ActiveCell.Row - 1), 1).Value
> otherwise, just bring the 2nd line up to the 1st line making it all one line.
> --
> Kevin Vaughn
>
>
> "Curt1521" wrote:
>
> > JLatham,
> > When I compiled it, I got a syntax error on this line.
> > Range(ActiveCell.End(xlDown).Address).Offset(1, 0) =
> >
> >

No problem . I would guess the line wrap problem is one of the most
problematic aspects of posting code here.
--
Kevin Vaughn


"JLatham" wrote:

> Thanks, Kevin, for clarifying that for Curt - the message editor here tried
> to eat my code and you caught it at it! Those 2 lines were all just one long
> line originally.
>
> "Kevin Vaughn" wrote:
>
> > That's a line wrap problem. the line you mention is continued on the next
> > line. If you want, you can use a line continuation character which is a
> > spcae followed by an underscore and nothing else on that line ( _)
> > Range(ActiveCell.End(xlDown).Address).Offset(1, 0) = _
> > ActiveCell.Offset(-(ActiveCell.Row - 1), 1).Value
> > otherwise, just bring the 2nd line up to the 1st line making it all one line.
> > --
> > Kevin Vaughn
> >
> >
> > "Curt1521" wrote:
> >
> > > JLatham,
> > > When I compiled it, I got a syntax error on this line.
> > > Range(ActiveCell.End(xlDown).Address).Offset(1, 0) =
> > >
> > >