Excel VBA - Last Column or Last Row

Find last column of a row, blank and very next to last updated cell

Sometimes in a sheet where we have a data in grid/table, we might want to periodically add data (pulled from somewhere dynamically) in very last column of that grid. For that we first need to identify the very last column on left which is blank and available for new entry.

We can use below VBA code. This will identify the column blank along the last populated column and will update word "Salary" as header there.

So if in Sheet1, we have some data in range A1 to F15, the below code if run, will update word "Salary" in Cell G1.

This is very simple, please try it and in case of any issue or question, please feel free to contact me.

Sub sbLastColumnOfARow()

Sheets("Sheet1").Select

With ActiveSheet

Lastheadcolumn = .Cells(1, .Columns.Count).End(xlToLeft).Column

LastheadcolumnFinal = Lastheadcolumn + 1

End With

NewcolumnLabel = "Salary"

Cells(1, LastheadcolumnFinal).Value = NewcolumnLabel

End Sub

Similarly finding the last populated row in a column is also easy through below code syntax.

'Find the last non-blank cell in column A(1)
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row

And below code is also on same lines. This will add the active cell's value in another sheet's specified column and in the next blank cell and in another respective column's same cell the value of 1 and will take back to the active cell again.

Sub ProductUpdate()

AddProduct = ActiveCell.Value

mysheet = ActiveSheet.Name

mycell = ActiveCell.Address

Sheets("Bill").Select

LastRow = Cells(Rows.Count, 5).End(xlUp).Row + 1

Cells(LastRow, 5).Value = AddProduct

Cells(LastRow, 7).Value = 1

Sheets("Products").Select

Sheets(mysheet).Range(mycell).Select

End Sub