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 ??
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 ??