General discussion

Locked

Visual Basic, Insert a column into Excel

By ramoore ·
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"
...

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by DKlippert In reply to Visual Basic, Insert a co ...

Thanks for asking the question!
Here's an answer:

Intersect(ActiveCell.CurrentRegion, ActiveCell.EntireColumn).FormulaR1C1 = ActiveCell.FormulaR1C1

http://www.mrexcel.com/archive/VBA/4423.html

Collapse -

by ramoore In reply to

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

Collapse -

by DKlippert In reply to Visual Basic, Insert a co ...

Or:

Intersect(ActiveCell.CurrentRegion, ActiveCell.EntireColumn).Value = "COMPLETE"

Collapse -

by ramoore In reply to

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

Collapse -

by DKlippert In reply to Visual Basic, Insert a co ...

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

Back to Web Development Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums