Inserting Columns

[BexleyManor]

Hi folks,

Once I've done the following I would dearly love to be able to insert two new columns to the left of the selected cell.

It's late and my mind is numb!!

[VBA]Sheet3.Range("IV2").End(xlToLeft).Select[/VBA]

[XLGibbs]

With ActiveCell
.Column.Insert Shift:=xlToRight
.Column.Insert Shift:=xlToRight
End With

would insert one...not sure of the best way to insert two with one line

[BexleyManor]

I get an invalid qualifier error with that code??

It flags the .Column ??

[XLGibbs]

Sorry about that..this is tested and more correct...

Sheet3.Range("IV2").End(xlToLeft).Activate
ActiveCell.Resize(, 2).EntireColumn.Insert

Speaking of Friday night numb minds...my apologies

[BexleyManor]

Yes, works splendidly!

Many thanks indeed.

[Zack Barresse]

Btw, it failed because Activecell.Column will return an integer/number and not a valid range, and Insert is a method of the range object.

(I ran out of toes and I'm sure I'm walking on some fingers now.. )

[BexleyManor]

Thanks for the input Zack, it's always nice to understand why thinks go wrong!

As an after thought to the code, once the two columns have been inserted I would also like some column headers adding.

As it stands the worksheet looks like..

PAC Period *inserted column* *inserted column* Total
B6/06 B6/06

so I'd like

PAC Period *PAC* *Period* Total
B06/06 B06/06 B07/06 B07/06


Any suggestions kind folks ??

[Norie]

Try this.
[vba]
Set rng = Sheet3.Range("IV2").End(xlToLeft)
rng.Resize(, 2).EntireColumn.Insert
rng.Offset(0, -2).Resize(, 2) = Array("PAC", "Period")[/vba]

[BexleyManor]

Thanks Norie. I was gonna ask how to perform the increments from P0606 to P0706 but in a moment of madness I came up with the following which draws from other elements of the workbook.

[VBA]Dim rng As Range, Yr As String

Yr = Right(Year(Now), 2)

Set rng = Sheet3.Range("IV3").End(xlToLeft)
rng.Resize(, 2).EntireColumn.insert
rng.Offset(-1, -2).Resize(, 2) = Array("Period", "Swap")
rng.Offset(0, -2).Resize(, 2) = Array("P" & Sheet12.cbPERIOD.Text & "/" & Yr)
[/VBA]

Sorted!! Thanks for all your help again folks, couldn't do it without you!

[XLGibbs]

Yr = Right(Year(Now), 2)

FYI, this can also be

Yr = Format(Date,"yy")

just so you know an alternative..glad you got it all worked out!

[Zack Barresse]

I guess since were throwing it all out there, you may want to look at changing this line ..

[vba]Set rng = Sheet3.Range("IV2").End(xlToLeft)[/vba]

.. to this ..

[vba]Set rng = Sheet3.Cells(2, Sheet3.Columns.Count).End(xlToLeft)[/vba]

In Excel 12, there will be more columns than just IV, so a test for the Columns.Count will encompass all versions.

[Norie]

firefytr

Do we really need the sheet reference here?
[vba]
Sheet3.Columns.Count[/vba]
Surely all sheets will have the same number of columns in the new version.

Unless for some fiendish(or not) reason Microsoft will keep sheets with only 65536 rows for backward compatibility, or something.

I mean, we can still use Excel 5.0 dialog sheets.

[Zack Barresse]

Yes, you do need the sheet reference Norie. I see what you're getting at, but it's just good habit. If you ever use any code with an addin or your Personal.xls workbook and you try to get the Rows.Count property when no workbook is active, it will fail. Why? Because there is no open workbook (activeworkbook) to get the Rows.Count from, as it's explicitly saying "use the activesheet, because there is no reference here". How do we combat this? We (always) explicitly set our worksheet references.

So just because we can Norie, doesn't mean that we should.

[Bob Phillips]

Do we really need the sheet reference here?
[vba]
Sheet3.Columns.Count[/vba]
Surely all sheets will have the same number of columns in the new version.

Yes you do. To demonstrate this, insert a chart sheet, go to the VB IDE and type ?Columns.Count in the Immediate window.

Unless for some fiendish(or not) reason Microsoft will keep sheets with only 65536 rows for backward compatibility, or something.

Now there is an interesting idea!