PDA

View Full Version : Inserting Columns



BexleyManor
01-20-2006, 05:33 PM
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!! :banghead:

Sheet3.Range("IV2").End(xlToLeft).Select

XLGibbs
01-20-2006, 05:51 PM
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
01-20-2006, 06:11 PM
I get an invalid qualifier error with that code??

It flags the .Column ??

XLGibbs
01-20-2006, 06:26 PM
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
01-20-2006, 07:25 PM
Yes, works splendidly!

Many thanks indeed.

Zack Barresse
01-20-2006, 09:48 PM
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.. :D)

BexleyManor
01-21-2006, 05:31 AM
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
01-21-2006, 06:42 AM
Try this.

Set rng = Sheet3.Range("IV2").End(xlToLeft)
rng.Resize(, 2).EntireColumn.Insert
rng.Offset(0, -2).Resize(, 2) = Array("PAC", "Period")

BexleyManor
01-21-2006, 05:29 PM
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.

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)


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

XLGibbs
01-21-2006, 06:40 PM
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
01-21-2006, 09:04 PM
I guess since were throwing it all out there, you may want to look at changing this line ..

Set rng = Sheet3.Range("IV2").End(xlToLeft)

.. to this ..

Set rng = Sheet3.Cells(2, Sheet3.Columns.Count).End(xlToLeft)

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

Norie
01-22-2006, 12:18 AM
firefytr

Do we really need the sheet reference here?

Sheet3.Columns.Count
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
01-23-2006, 08:22 AM
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
01-23-2006, 08:26 AM
Do we really need the sheet reference here?

Sheet3.Columns.Count
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!