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.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
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.
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.
- 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?