PDA

View Full Version : [SOLVED] Find First Cell in Column Containing non-NULL Data



GarysStudent
02-19-2016, 09:24 AM
At the top of column A are an unknown number of empty cells. Below them are an unknown number of cells with formulas returning Null. Below them are cells containing formulas returning data that is not Null (cells with LEN>0). I am trying to find some form of .Find() to locate the first cell with with non-Null data. This sub:


Sub FindCellNotNull()
Dim rng As Range, r As Range
Set rng = Range("A:A")
Set r = rng.Find(what:="*", after:=rng(1))
MsgBox r.Row
End Sub




gets me the first cell that returns Null but this is not what I need. I can get the first cell using:


Sub FindCellNotNull2()
Dim rng As Range, r As Range
Set rng = Range("A:A")
For Each r In rng
If r.Value <> "" Then
MsgBox r.Row
Exit Sub
End If
Next r
End Sub




but I really prefer not using a loop. Is there some form of .Find() that I can use ??

p45cal
02-20-2016, 03:39 AM
just a guess:
what:="?*"

Other points:
1. If the top cell of the column has something in it you want to find you might not find it with after:=rng(1) because it'll start the search a cell below (after) that top cell; you can use after:=rng(rng.rows.count) instead.
2. the arguments LookIn, LookAt, SearchOrder, and SearchFormat are saved each time you use this method, whether used from the sheet or from code so it would do to specify them each time:
LookAt can be xlWhole or xlPart (would your current code work if this was specified as xlPart? I don't know - try it.)
lookIn can be xlvalues or xlFormulas, in this case I think it needs to be xlValues.
SearchFormat needs to be False.
SearchOrder doesn't matter for single columns or single rows.

snb
02-20-2016, 05:16 AM
Sub M_snb()
MsgBox Cells([small((A1:A1000<>"")*row(1:1000),sum(N(A1:A1000=""))+1)], 1).Address
End Sub

GarysStudent
02-20-2016, 08:07 AM
Thank you very much for your help, especially the tutorial material. Sadly using ?* rather than * did not solve the problem.

GarysStudent
02-20-2016, 08:16 AM
Thanks for:



reminding about EVALUATE or [...]
showing me that the [...] syntax does not require doubling up on the double quote
taking the tell to help me


Since the [...] syntax supports both normal worksheet formulas as well as array worksheet formulas, I am using:


Sub Pseudo_snb()
MsgBox [match(FALSE,a1:a1000="",0)]
End Sub

snb
02-20-2016, 08:51 AM
Much better :clap:

shrivallabha
02-22-2016, 06:47 AM
To answer the original query, Find can be used in following way.

Sub Test()
MsgBox Cells.Columns(1).Find("*", [A1],xlValues, xlPart, xlByRows, xlNext).Address
End Sub
One word of caution which you might be aware. It alters the Find dialog settings.