Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 88
  • Last Modified:

FIND THE FIRST NON BLANK CELL IN A RANGE

i am trying to find the first blank cell in range $F$15:$L$17  starting from f15,f16,f17 etc. thanks
0
Svgmassive
Asked:
Svgmassive
  • 3
  • 2
  • 2
  • +2
2 Solutions
 
Glenn RayExcel VBA DeveloperCommented:
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
 
Glenn RayExcel VBA DeveloperCommented:
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
 
SvgmassiveAuthor Commented:
example attached.
thank you
first-blank.xlsb
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Martin LissRetired ProgrammerCommented:
Dim r As Range
Set r = Range($F$15:$L$17).SpecialCells(xlCellTypeBlanks)
MsgBox r.Row

Open in new window

0
 
SvgmassiveAuthor Commented:
sorry martin not what i am looking for
0
 
Glenn RayExcel VBA DeveloperCommented:
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
 
Rob HensonIT & Database AssistantCommented:
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
 
Hakan YılmazTechnical Office MEP EngineerCommented:
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
 
Hakan YılmazTechnical Office MEP EngineerCommented:
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now