Search and Add Data in Empty Row Excel

This tutorial explains how to search and add your data in a empty row.

WorksheetFunction.CountA method is used in this tutorial to find the a empty row in your excel sheet. There are two method to add data in a empty row first is from cell to a cell and other is from userform to a cell. Both methods are explained very well below.

WorksheetFunction.CountA method

Counts the number of cells that are not empty and the values within the list of arguments

Syntax
expression.CountA (Arg1, Arg2, Arg3, Arg4, Arg5, Arg6, Arg7, Arg8, Arg9, Arg10, Arg11, Arg12, Arg13, Arg14, Arg15, Arg16, Arg17, Arg18, Arg19, Arg20, Arg21, Arg22, Arg23, Arg24, Arg25, Arg26, Arg27, Arg28, Arg29, Arg30)

Parameters

Name	Required/Optional	Data type	Description
Arg1 - Arg30	Required	Variant	1 to 30 arguments representing the values that you want to count.

Use CountA to count the number of cells that contain data in a range or array.

A value is any type of information, including error values and empty text (""). A value does not include empty cells.

If an argument is an array or reference, only values in that array or reference are used. Empty cells and text values in the array or reference are ignored.

How to Use CountA Function

Add Data From Cell to Cell

Private Sub CommandButton1_Click()

Dim emptyRow As Long

emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

For j = 1 To 2

            Cells(emptyRow, j).Value = Cells(2, j + 7).Value

        Next j

End Sub

j+7, 7 is used because when j is 1 so it will 1+7=8 mean H Column. Like 2+7=9, I column data will be added in A and B column empty row.

Add Data From UserForm to Cell

Private Sub CommandButton1_Click()

Dim emptyRow As Long

emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

 For j = 1 To 2

            Cells(emptyRow, j).Value = Me.Controls("TextBox" & j).Value

        Next j

End Sub

Watch Complete Tutorial Here,