Solved

# FIND THE FIRST NON BLANK CELL IN A RANGE

Posted on 2014-12-03
78 Views
i am trying to find the first blank cell in range \$F\$15:\$L\$17  starting from f15,f16,f17 etc. thanks
0
Question by:Svgmassive
• 3
• 2
• 2
• +2

LVL 27

Expert Comment

ID: 40478718
Shortcut keystrokes:  Put your cursor in F15, then press [End] and then the [Down Arrow] key.

If you're looking for another solution (function or programmatic), please provide more specifics.

Regards,
-Glenn
0

LVL 27

Expert Comment

ID: 40478730
I also need to point out that your question title states "non blank cell" but your question says "first blank cell".  We'll need to know which one you really want if you're looking for another solution.
0

Author Comment

ID: 40478741
example attached.
thank you
first-blank.xlsb
0

LVL 45

Expert Comment

ID: 40478752
``````Dim r As Range
Set r = Range(\$F\$15:\$L\$17).SpecialCells(xlCellTypeBlanks)
MsgBox r.Row
``````
0

Author Comment

ID: 40478777
sorry martin not what i am looking for
0

LVL 27

Assisted Solution

Glenn Ray earned 250 total points
ID: 40478784
Tweaking Martin's code (missing quotes) to show all addresses with blanks:
``````Sub Blank_List()
Dim r As Range
Set r = Range("\$F\$15:\$L\$17").SpecialCells(xlCellTypeBlanks)
End Sub
``````

However, if you want the address of the first blank cell of a range when scanned first by rows, then columns, this code will provide the address:
``````Sub First_Blank()
Dim rng As Range
Dim cl As Object
Dim intRow As Integer
For intRow = 15 To 17
Set rng = Range("\$F\$" & intRow & ":\$L\$" & intRow)
For Each cl In rng
If IsEmpty(cl) Then
Exit Sub
End If
Next cl
Next intRow
'no blank found - message
MsgBox "No empty cells found in the specified range.", vbExclamation + vbOKOnly, "Notice"
End Sub
``````

If you removed the For intRow = 15 to 17 loop and selected the entire range, the For Each...Next would scan by columns, then rows, returning \$G\$15 instead.

Regards,
-Glenn
EE-first-blank.xlsm
0

LVL 31

Expert Comment

ID: 40478838
I assume you are wanting this as part of another script so that you can get it to enter a value into the first blank cell in the range.

If so how about for this logic:

Start at F15 - Do "End Right"
If active column is less than 12 you haven't reached L, go one more right and populate as required;
If column is 12 (or greater) you have reached L (or beyond) so move back to F16 and repeat;
repeat for row 17 if required.

Thanks
Rob H
0

LVL 5

Accepted Solution

Hakan Yılmaz earned 250 total points
ID: 40482451
I attach a sample with a custom function that gives the address of first blank cell in given range. First looks at columns and then rows.
first-blank.xlsb
0

LVL 5

Expert Comment

ID: 41682694
I noticed a mistake in attached file. (It was working, but for different range).
Updated function is below.

``````Option Explicit

Function firstblankinrange(ByRef myrange As Range) As String
Dim itercol As Integer
Dim iterrow As Integer
For itercol = 1 To myrange.Columns.Count
For iterrow = 1 To myrange.Rows.Count
If myrange.Cells(iterrow, itercol).Value = "" Then
Exit Function
End If
Next iterrow
Next itercol
End Function
``````
0

## Featured Post

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.