How to create VBA code to find next empty column and next empty row

< Back to Search results

 Category: [Excel]  Demo Available 

How to create VBA code to find next empty column and next empty row




The user has started an application in Excel.

Column 1 or A contains codes of people names.

The user wants the vba code to find the next empty column (which will be B).

The user wants to place data in B until i have reached the length of FILLED column A (Find the next empty row in column B).

Only then the user wants the code to find the next empty column.

The code to find the next empty column is as follows and works just fine:

Dim NextEmptyCol As Long
NextEmptyCol = Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column + 1

MsgBox "Column Number" & " " & NextEmptyCol & vbCr & _
"Or column letter """ & Replace(Cells(1, NextEmptyCol).Address(0, 0), 1, "") & """", vbInformation, "The next empty Column is ..."



The user needs assistance filling column B with data until the length of filled column A had been reached.




You can navigate the cell like...

Sub data_Input()
 Dim lngRow As Long
 Dim lngCol As Long
 Dim lngRowInput As Long
 Dim lngColInput As Long

 lngRow = Cells(Rows.Count, "A").End(xlUp).Row
 lngCol = Cells(2, Columns.Count).End(xlToLeft).Column
 lngRowInput = Cells(Rows.Count, lngCol).End(xlUp).Row

If lngRowInput >= lngRow Then
   lngColInput = lngCol + 1
   lngRowInput = 2
 lngColInput = lngCol
 lngRowInput = lngRowInput + 1
End If

Cells(lngRowInput, lngColInput) = "x"
End Sub


Obtained from the OzGrid Help Forum.

Solution provided by pike.


See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets


See also:

How to archive rows from one worksheet to another based on the value of a cell in each row
How to copy 300 rows at a time from one column with 3K rows and convert to csv file
How to copy data from certain columns in a row from one sheet to another
How to move row contents to another sheet based on criteria


Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.


stars (0 Reviews)