Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add a new row under a changing list
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add a new row under a changing list
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 WL wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add a new row under a changing list
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 -- Don Guillett SalesAid Software "WL" wrote in message oups.com... 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add a new row under a changing list
Thanks a lot... It was of great help!!! =)
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add a new row under a changing list
glad to help
-- Don Guillett SalesAid Software "Daphne" wrote in message oups.com... Thanks a lot... It was of great help!!! =) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to Change List Based on Value Chosen in Another List | Excel Worksheet Functions | |||
Changing the name of a list. | Excel Discussion (Misc queries) | |||
Changing the color of a list entry based on a tolerance | Excel Worksheet Functions | |||
Need to find oldest date in ever changing list. | Excel Worksheet Functions | |||
Refresh a Validation List? | Excel Discussion (Misc queries) |