Macro to insert and fill a new column

M

Michael151

Board Regular
Joined
Sep 20, 2010
Messages
247
Hello all,

I’m trying to write a macro that will create a column after the last column in a sheet and fill in that column until the last row in column A.

Before sheet:

Row1 Col1 Col2
Row2 Col1 Col2
Row3 Col1 Col2
Row4 Col1 Col2
Row5 Col1 Col2

After Macro:

Row1 Col1 Col2 NewColumn
Row2 Col1 Col2 NewCol
Row3 Col1 Col2 NewCol
Row4 Col1 Col2 NewCol
Row5 Col1 Col2 NewCol

The macro will need to insert a header in row 1 labeled “NewColumn” and fill down with the word “NewCol” until the last row in Column A.

Any help is most appreciated – thanks!
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
Like this?
Code: 
Sub newfilledcol()
Dim lstcol As Long, lstrowA As Long
lstcol = Cells.Find("*", LookIn:=xlValues, after:=[a1], _
    searchorder:=xlByColumns, searchdirection:=xlPrevious).Column
lstrowA = Range("A:A")(Rows.Count).End(3).Row
Cells(1, lstcol + 1).Resize(lstrowA) = "NewCol"
Cells(1, lstcol + 1) = "NewColumn"
End Sub
 
Upvote 0
Hi mirabeau,

One thing I forgot to mention in my original post is - if no data is found, can the macro do nothing?

That is - if the sheet is completely blank and there is no last row, then do not fill with anything.

Thanks!
 
Upvote 0
I get an error message saying "object block variable or with block variable not set" when I try running with no data.
 
Upvote 0
OK. The "Find" approach does give that error if there's nothing to find.

To remedy, modify the code with a couple of extra lines
Rich (BB code): 
Sub newfilledcol()
Dim lstcol As Long, lstrowA As Long
If Not Cells.Find("*") Is Nothing Then
    lstcol = Cells.Find("*", LookIn:=xlValues, after:=[a1], _
        searchorder:=xlByColumns, searchdirection:=xlPrevious).Column
    lstrowA = Range("A:A")(Rows.Count).End(3).Row
    Cells(1, lstcol + 1).Resize(lstrowA) = "NewCol"
    Cells(1, lstcol + 1) = "NewColumn"
End If
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
Finding the differences then copy and paste code to a new sheet
Replies
1
Views
663
myall_blues
myall_blues
S
  • Solved
Copying Filtered Columns (non-adjacent) from one Table to end of another, but need to also add information in adjacent column of Destination Table
Replies
1
Views
579
sspatriots
S
N
  • Solved
Find next visible cell, add a formula and then fill column down
Replies
8
Views
409
MARK858
MARK858
Q
  • Question Question
Macro needed to insert 4 rows in table, copy data from above, copy data from another sheet and paste special (transpose values) to those new rows...re
Replies
1
Views
383
sijpie
sijpie
M
  • Solved
Copy the last sheet from file to another with ignore empty whole column
Replies
7
Views
388
mumps
mumps

Forum statistics

Threads
1,224,603
Messages
6,179,849
Members
452,948
Latest member
UsmanAli786

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top