How to find first blank cells in column in Excel?
For a list of data, there may be populated with some blank cells. For finding the first blank cell in the selected range of the column, please browse the below tutorial for more details.
Find first blank cell in column with formula
Find first blank cell in column with VBA code
Find first blank cell in column with formula
In this section, we will show you how to find the first blank cell in column with formula. Take below list of data as example, please do as follows.
1. Select a blank cell for displaying the result.
2. Copy and paste formula =MIN(IF(A1:A23="",ROW(A1:A23))) into the Formula Bar, then press Ctrl + Shift + Enter keys on your keyboard. See screenshot:
Note: Please change the range in the formula to your own range.
Then you will get a row number showing in the selected cell. As your specified range is in the A column, based on the number it displayed, it means that the first blank cell is A16.
Find first blank cell in column with VBA code
Besides the formula, you can also find first blank cell in column with VBA code. Please do as follows.
1. Press Alt + A11 at the same time to open the Microsoft Visual Basic for Applications window.
2. In the Microsoft Visual Basic for Applications window, double click the current using worksheet name in the left bar to open the code editor.
3. Copy and paste the following VBA code into the editor.
VBA code: find first blank cell in column
Sub Findfirstblankcell()
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type: = 8)
For Each Rng In WorkRng
If Rng.Value = "" Then
MsgBox "No Value, in " & Rng.Address
End If
Next
End Sub
4. Click the Run button or press the F5 key to run the code.
5. After running the code, a KutoolsforExcel dialog box pops up, please select the range you want to find the first blank cell, then click the OK button. See screenshot:
Then a dialog box pops up with the address of the first blank cell in the specified column listed inside.
Note: If there are other blank cells in the selected range, the above dialog box will keep popping up until it finish finding all blank cells in the range.
Related articles:
- How to find position of first/last number in a text string in Excel?
- How to find cells with/having conditional formatting in Excel?
- How to find cells with/having data validation in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
