Web Development

Ask something
Discussion Locked

Visual Basic, Insert a column into Excel

0 helpful
5 replies

by ramoore

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I writing a VB program that inserts a column of cells into an existing Excel worksheet with a specified range (e.g. "A1:A20"). After the column is inserted, each cell up to row 20 is filled with a text string.
How can I insert a column of cells without specifying the range? In other words, I would like to fill each cell in the new column and stop when it reaches the last row of data in the other existing columns. Any help is appreciated!
...
'specify a range of cells and fill with text
Selection.Insert
Range("A1:A20").Value = "MY TEXT"
...

5 replies

by ramoore

Being relatively new to VB, it looks as though you supplied me code that inserts a formula in all active cells in a column. Which is great, but I need to simply add the word "COMPLETE" to each cell to the last row.

Does something like this look OK?...
Selection.Insert
Selection.Value = "COMPLETE"
Intersect(ActiveCell.CurrentRegion, ActiveCell.EntireColumn).Value = ActiveCell.Value

by ramoore

DKlippert, thanks for your help and patience. The code works great, but the new column overwrites my existing column cells in "A1". I need to have the new column inserted in "A1" and shift my existing other columns over one.
Sorry if this is clear as mud, but can you help one more time?

A1|B1|C1|D1|
------------------------
COMPLETE|data|data|data

by dklippert

Sub Complete()
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Intersect(ActiveCell.CurrentRegion, ActiveCell.EntireColumn).Value = "COMPLETE"
End Sub

This discussion is closed to new replies.