Add a new row under a changing list

W

WL

Hi all,

I need to know how to:

record a macro that will do this action:

1. select a cell with words,
2.Ctrl+ --> to go to the last connected cell with word,
3.move --> again to reach an empty cell,
4.insert a new column.

I tried recording it in this manner, but when i insert a row in, it
goes according to the specific column like H:H

How should i do it?

thanks a lot.
 
J

jkend69315

WL,
You're probably getting recorded code something like this:
Range("A5").Select
Selection.End(xlToRight).Select
Columns("E:E").Select
Selection.Insert Shift:=xlToRight
You're not going to get Excel to understand that you want the recorded
code to always show the column of the first available cell to the
right. But it's easy to fix the code by replacing
Columns("E:E").Select
with
Columns(ActiveCell.Column + 1).Select

Record macro is a great tool, but it won't always give you exactly what
you have in mind, and it often records unneeded stuff. For instance,
it's hardly ever necessary to select things to accomplish what you want
to do. So, you will usually want to clean up recorded code to be more
efficient. Anyway, replacing this single line of code is the easiest
way to get the recorded macro to do what you want.
Cheers!
James
 
D

Don Guillett

One looks to the end and comes back to find the last column
Sub lastcolumninsertfromend()
Columns(Cells(ActiveCell.Row, Columns.Count) _
..End(xlToLeft).Column).Insert
End Sub

this goes from where you are to the right
Sub lastcolumninsertcell()
Columns(Cells(ActiveCell.Row, ActiveCell.Column) _
..End(xlToRight).Column).Insert
End Sub
 
Top