Solved

FIND THE FIRST NON BLANK CELL IN A RANGE

Posted on 2014-12-03
9
83 Views
Last Modified: 2016-07-06
i am trying to find the first blank cell in range $F$15:$L$17  starting from f15,f16,f17 etc. thanks
0
Comment
Question by:Svgmassive
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 27

Expert Comment

by:Glenn Ray
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

by:Glenn Ray
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

by:Svgmassive
ID: 40478741
example attached.
thank you
first-blank.xlsb
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 47

Expert Comment

by:Martin Liss
ID: 40478752
Dim r As Range
Set r = Range($F$15:$L$17).SpecialCells(xlCellTypeBlanks)
MsgBox r.Row

Open in new window

0
 

Author Comment

by:Svgmassive
ID: 40478777
sorry martin not what i am looking for
0
 
LVL 27

Assisted Solution

by:Glenn Ray
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)
    MsgBox r.Address
End Sub

Open in new window


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
                MsgBox cl.Address
                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

Open in new window


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 33

Expert Comment

by:Rob Henson
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

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

by:Hakan Yılmaz
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
                firstblankinrange = myrange.Cells(iterrow, itercol).Address
                Exit Function
            End If
        Next iterrow
    Next itercol
End Function

Open in new window

0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Filter an Excel list by multiple criteria 6 40
Need help with an Excel Formula 2 29
Formula returning #N/A 9 35
Excel - Filter Copy/Paste Unique Items 5 23
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
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…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question