Macro to insert and fill a new column

[Michael151]

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!

 

[mirabeau]

Like this?

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

 

[Michael151]

That worked Mirabeau - thanks!!

 

[Michael151]

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!

 

[Michael151]

I get an error message saying "object block variable or with block variable not set" when I try running with no data.

 

[mirabeau]

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

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

 

[Michael151]

Thanks!!