VBA & Macro Problems

Excel VBA: Enter Data After the Last Used Column

Tauseeq MagsiTauseeq Magsi Nov 18, 2026 868 views

Question details

Write a value to the column immediately after the last used column in a specified row using Excel VBA.

Enter Data After the Last Used Column with 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.
Before you start

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.

Solution 1Recommended

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.

1
Open the VBA editor

In Excel, open the Developer tab and select Visual Basic, then choose Insert > Module.

2
Add the macro

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

3
Set the worksheet and row

Replace "Sheet1" with your worksheet name. To use another row, replace each row 1 reference in Cells and Rows with the desired row number.

4
Run the macro

Return to Excel, select Developer > Macros, choose WriteAfterLastColumn, and click Run.

Use Cells to Write to the Next Column in Excel VBA
How the column is selected: Cells(row, column) uses numeric row and column arguments. The macro writes to column A if the row is empty and stops if the rightmost column already contains a value.
Free Microsoft Office alternative

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.

Use a familiar spreadsheet interface to work with rows and columns.Open and edit Microsoft Excel formats such as XLSX.Move existing spreadsheet data into a lightweight Office alternative.Use core spreadsheet tools for free.
microsoft office alternative - wps office

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.