OzGrid

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

Requirement:

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.

Solution:

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
Else
 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:

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.

Gallery