Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert column, move data with VBA
Ok here is what I have:
Columns with several rows of names followed by several rows of numbers, I need to cut the numbers out, insert a new column and paste them in the new column. I have tried several things but can't quite get it. I have many columns to do this with so I need to loop through a range thanks!! Fred BEFO A B --------------------- Fred Amy Dave Jan Sam Bev Doug Carol 1 1 2 2 3 3 4 4 5 5 6 6 AFTER: A B C D ------------------------------------------ Fred 1 Amy 1 Dave 2 Jan 2 Sam 3 Bev 3 Doug 4 Carol 4 5 5 6 6 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert column, move data with VBA
Dim lastcol as Long, i as long, rng as Range
lastcol = cells(1,"IV").End(xltoleft).Column for i = lastcol to 1 step -1 columns(i+1).Insert Set rng = Nothing On Error Resume Next set rng = columns(i).SpecialCells(xlconstants,xlnumbers) On Error goto 0 if not rng is nothing then rng.copy cells(i+1,1) rng.Clearcontents end if Next -- Regards, Tom Ogilvy "Fred" wrote in message oups.com... Ok here is what I have: Columns with several rows of names followed by several rows of numbers, I need to cut the numbers out, insert a new column and paste them in the new column. I have tried several things but can't quite get it. I have many columns to do this with so I need to loop through a range thanks!! Fred BEFO A B --------------------- Fred Amy Dave Jan Sam Bev Doug Carol 1 1 2 2 3 3 4 4 5 5 6 6 AFTER: A B C D ------------------------------------------ Fred 1 Amy 1 Dave 2 Jan 2 Sam 3 Bev 3 Doug 4 Carol 4 5 5 6 6 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert column, move data with VBA
Thanks Tom... the columns insert fine but the numbers do not move they
just disappear... any ideas? Fred |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert column, move data with VBA
Sorry, had a typo:
Sub efg() Dim lastcol As Long, i As Long, rng As Range lastcol = Cells(1, "IV").End(xlToLeft).Column For i = lastcol To 1 Step -1 Columns(i + 1).Insert Set rng = Nothing On Error Resume Next Set rng = Columns(i).SpecialCells(xlConstants, xlNumbers) On Error GoTo 0 If Not rng Is Nothing Then rng.Copy Cells(1, i + 1) rng.ClearContents End If Next End Sub -- Regards, Tom Ogilvy "Fred" wrote in message ups.com... Thanks Tom... the columns insert fine but the numbers do not move they just disappear... any ideas? Fred |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert column, move data with VBA
Tom you are awesome as usual!! That worked perfect!! Thanks again!!
Fred |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to move the cursor to column A after entering data column F | New Users to Excel | |||
How can I cut data from one column and move it to another. | New Users to Excel | |||
Insert sheet, move to end, rename with cell data. | Excel Discussion (Misc queries) | |||
insert column move object | Excel Discussion (Misc queries) | |||
create macro to move label type data to column data | Excel Programming |