Excel VBA: Enter Data After the Last Used Column
Question details
Write a value to the column immediately after the last used column in a specified row using Excel VBA.

- Product
- Microsoft Excel VBA
- Device & OS
- not provided
- Scenario
- A macro needs to find the next available column in a worksheet row and enter a value there.
- Observed behavior
- Combining a cell address with a column number can produce an unintended reference instead of targeting the calculated column.
Identify the worksheet name and row where the value should be entered. Open the workbook in a version of Excel that supports VBA, and save it as a macro-enabled workbook if you need to keep the macro.
Use Cells to Write to the Next Column in Excel VBA
Find the last used column in row 1 and write to the following column, while handling empty and full rows.
In Excel, open the Developer tab and select Visual Basic, then choose Insert > Module.
Paste this code into the module: Sub WriteAfterLastColumn() Dim ws As Worksheet Dim lastCol As Long Set ws = ThisWorkbook.Worksheets("Sheet1") If ws.Cells(1, ws.Columns.Count).Value <> "" Then MsgBox "There is no column available to the right." Exit Sub ElseIf Application.WorksheetFunction.CountA(ws.Rows(1)) = 0 Then lastCol = 1 Else lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column + 1 End If ws.Cells(1, lastCol).Value = "Yes" End Sub
Replace "Sheet1" with your worksheet name. To use another row, replace each row 1 reference in Cells and Rows with the desired row number.
Return to Excel, select Developer > Macros, choose WriteAfterLastColumn, and click Run.

Work with Excel Spreadsheets in WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open compatible Excel files and continue working with your data in WPS Spreadsheets; VBA support can depend on the WPS edition and configuration.

Frequently Asked Questions
How do I write to a different row?
In the macro, replace every row 1 reference in ws.Cells and ws.Rows with the desired row number. Use the same row for finding the last column and writing the value.
What happens if the row is empty?
The CountA check detects an empty row and sets the target column to 1, so ws.Cells writes the value in column A.
Can I use a worksheet other than Sheet1?
Yes. Change ThisWorkbook.Worksheets("Sheet1") to the name of the target worksheet in the workbook containing the macro.
Why use Cells instead of joining an address and a number?
Cells accepts the row and column as separate numeric arguments. Joining an existing address and a number can create an unintended cell reference.
How do I save the VBA macro?
In Excel, select File > Save As and choose Excel Macro-Enabled Workbook (.xlsm) as the file type.




