cant add a column in vb code
 

I have opened a spreadsheet and am trying to add a column

if I use the following

selection.Insert Shift:=xlToRight

this works on the first iteration but fails on the second with the
following error
object variable or with block not set.

If i try the following

xlWks.Columns.Insert Shift:=xlToRight i get the message
cannot shift non blank cells off worksheet.

However the worksheet is off limited size (36 rows and 20 columns) and
is
borfered by blank cells none of which have associated formula


--
helenmacduff
------------------------------------------------------------------------
helenmacduff's Profile: http://www.excelforum.com/member.php...o&userid=29020
View this thread: http://www.excelforum.com/showthread...hreadid=487525

cant add a column in vb code
 

Helen

borfered by blank cells none of which have associated formula

....You may think so, but if you press Ctrl and right arrow you will find
that data extends way beyond where you think it is... Inserting cells,
whether they now have data/formulas or not will be seen by Excel as 'Used'

You second example is moving the columns collection (the whole lot) to the
right, that will not work as it tries to move 256 columns to another 256
'spaces' to the right that don't exist.

You could try resetting the usedrange after each insert and saving, if you
persist with the first route

Sheet1.UsedRange
ActiveWorkbook.Save

If that doesn't work then try deleting entire columns and saving the
workbook (Similar to your second route)

Selection.EntireColumn.Insert

HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"helenmacduff"
wrote in message
news:helenmacduff.1yxtda_1132728602.0501@excelforu m-nospam.com...

I have opened a spreadsheet and am trying to add a column

if I use the following

selection.Insert Shift:=xlToRight

this works on the first iteration but fails on the second with the
following error
object variable or with block not set.

If i try the following

xlWks.Columns.Insert Shift:=xlToRight i get the message
cannot shift non blank cells off worksheet.

However the worksheet is off limited size (36 rows and 20 columns) and
is
borfered by blank cells none of which have associated formula

helenmacduff
------------------------------------------------------------------------
--

helenmacduff's Profile:
http://www.excelforum.com/member.php...o&userid=29020
View this thread: http://www.excelforum.com/showthread...hreadid=487525

cant add a column in vb code
 

the selection is a method off xlapp

so xlapp.selection.entirecolumns.insert works


--
helenmacduff
------------------------------------------------------------------------
helenmacduff's Profile: http://www.excelforum.com/member.php...o&userid=29020
View this thread: http://www.excelforum.com/showthread...hreadid=487525