inserting columsns variable range

J

joemeshuggah

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!
 
K

ker_01

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
 
J

joemeshuggah

Thanks...this appears to be adding rows, however...

ker_01 said:
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 said:
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!
 
K

ker_01

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 said:
Thanks...this appears to be adding rows, however...

ker_01 said:
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 said:
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!
 
J

joemeshuggah

i must have jumbled something after trying alternatives...this works
perfectly...thanks!

ker_01 said:
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 said:
Thanks...this appears to be adding rows, however...

ker_01 said:
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

:

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top