Find first blank with VBA, but Range has a formula

[Razor303]

Hi, I need to find the first blank in coloum A with VBA code, I have A formula in that coloum returning ether a number 1 or a blank.
I ve tryed setting a varable Firstblank = Cells(Rows.Count, 1 ).End(xlDown).Row but its going down to the first empty cell were is no formula. not were the first blank is that the formula has returned Thanks to anyone how can help me with this.

 

[jasmith4]

    Dim R As Range
 
    Set R = Range("A1")
    Do While Len(R.Text) > 0
        Set R = R.Offset(1, 0)
    Loop

You might need to change the condition to

Len(Trim$(R.Text)) > 0

 

[VoG]

Maybe

LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row

 

[JackDanIce]

Hi,

Not sure I fully understand what you are trying to do, but you could try recording this macro and then analysing the code:

Press F5
Click button "Special"
Select "Formulas",
Un-select "Numbers", "Logicals" and "Errors"
Then click "Ok"

And see if the generated code helps or you can modify?

 

[mirabeau]

Hi, I need to find the first blank in coloum A with VBA code, I have A formula in that coloum returning ether a number 1 or a blank.
I ve tryed setting a varable Firstblank = Cells(Rows.Count, 1 ).End(xlDown).Row but its going down to the first empty cell were is no formula. not were the first blank is that the formula has returned Thanks to anyone how can help me with this.

To do this, try

Range("A" & Evaluate("match(true," & Range("A:A").Address & "="""",0)")).Select

 

[Razor303]

Thanks a lot mirabeau that's working Great. now I can get on with my project.

 

[mirabeau]

Thanks a lot mirabeau that's working Great. now I can get on with my project.

Hey Razor303,

Thanks for the acknowledgment. That's always v much appreciated.