Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
inserting columsns variable range
hello all,
i have a spreadsheet that is variable in range (number of columns can vary). i would like to create a macro that for each column in the worksheet, two new columns would be inserted to the left. i thought this would work, but i am running into problems... LastColumn = Cells.Find(What:="*", After:=[A1], _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious).Column LC = LastColumn For i = 2 To LC If Cells(5, i + 1) < "" Then Cells(5, i).Select Selection.EntireColumn.Insert Selection.EntireColumn.Insert End If Next i how do i go about accomplishing this? thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
inserting columsns variable range
As you insert each column, you move the remaining columns to the right, so
your total count is no longer accurate. Try looping through from right to left instead of left to right; that way your extra columns won't affect the "unprocessed" portion of your worksheet: For i = LC to 2 step -1 'your code HTH, Keith "joemeshuggah" wrote: hello all, i have a spreadsheet that is variable in range (number of columns can vary). i would like to create a macro that for each column in the worksheet, two new columns would be inserted to the left. i thought this would work, but i am running into problems... LastColumn = Cells.Find(What:="*", After:=[A1], _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious).Column LC = LastColumn For i = 2 To LC If Cells(5, i + 1) < "" Then Cells(5, i).Select Selection.EntireColumn.Insert Selection.EntireColumn.Insert End If Next i how do i go about accomplishing this? thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
inserting columsns variable range
Thanks...this appears to be adding rows, however...
"ker_01" wrote: As you insert each column, you move the remaining columns to the right, so your total count is no longer accurate. Try looping through from right to left instead of left to right; that way your extra columns won't affect the "unprocessed" portion of your worksheet: For i = LC to 2 step -1 'your code HTH, Keith "joemeshuggah" wrote: hello all, i have a spreadsheet that is variable in range (number of columns can vary). i would like to create a macro that for each column in the worksheet, two new columns would be inserted to the left. i thought this would work, but i am running into problems... LastColumn = Cells.Find(What:="*", After:=[A1], _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious).Column LC = LastColumn For i = 2 To LC If Cells(5, i + 1) < "" Then Cells(5, i).Select Selection.EntireColumn.Insert Selection.EntireColumn.Insert End If Next i how do i go about accomplishing this? thanks! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
inserting columsns variable range
I can't think of a reason why selection.entirecolumn.insert would be
inserting rows. The following was tested in XL03 and works. HTH, Keith Sub testing() LC = Cells.Find(What:="*", After:=[A1], _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious).Column For i = LC To 2 Step -1 Cells(5, i).Select Selection.EntireColumn.Insert Selection.EntireColumn.Insert Next i End Sub "joemeshuggah" wrote: Thanks...this appears to be adding rows, however... "ker_01" wrote: As you insert each column, you move the remaining columns to the right, so your total count is no longer accurate. Try looping through from right to left instead of left to right; that way your extra columns won't affect the "unprocessed" portion of your worksheet: For i = LC to 2 step -1 'your code HTH, Keith "joemeshuggah" wrote: hello all, i have a spreadsheet that is variable in range (number of columns can vary). i would like to create a macro that for each column in the worksheet, two new columns would be inserted to the left. i thought this would work, but i am running into problems... LastColumn = Cells.Find(What:="*", After:=[A1], _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious).Column LC = LastColumn For i = 2 To LC If Cells(5, i + 1) < "" Then Cells(5, i).Select Selection.EntireColumn.Insert Selection.EntireColumn.Insert End If Next i how do i go about accomplishing this? thanks! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
inserting columsns variable range
i must have jumbled something after trying alternatives...this works
perfectly...thanks! "ker_01" wrote: I can't think of a reason why selection.entirecolumn.insert would be inserting rows. The following was tested in XL03 and works. HTH, Keith Sub testing() LC = Cells.Find(What:="*", After:=[A1], _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious).Column For i = LC To 2 Step -1 Cells(5, i).Select Selection.EntireColumn.Insert Selection.EntireColumn.Insert Next i End Sub "joemeshuggah" wrote: Thanks...this appears to be adding rows, however... "ker_01" wrote: As you insert each column, you move the remaining columns to the right, so your total count is no longer accurate. Try looping through from right to left instead of left to right; that way your extra columns won't affect the "unprocessed" portion of your worksheet: For i = LC to 2 step -1 'your code HTH, Keith "joemeshuggah" wrote: hello all, i have a spreadsheet that is variable in range (number of columns can vary). i would like to create a macro that for each column in the worksheet, two new columns would be inserted to the left. i thought this would work, but i am running into problems... LastColumn = Cells.Find(What:="*", After:=[A1], _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious).Column LC = LastColumn For i = 2 To LC If Cells(5, i + 1) < "" Then Cells(5, i).Select Selection.EntireColumn.Insert Selection.EntireColumn.Insert End If Next i how do i go about accomplishing this? thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inserting variable outputs into different ranges | Excel Programming | |||
Using variable name as a value for inserting a row | Excel Programming | |||
Inserting Variable Number of Rows | Excel Programming | |||
inserting a variable into VB | Excel Programming |