Find first blank with VBA, but Range has a formula

R

Razor303

New Member
Joined
Jan 16, 2011
Messages
22
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.
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
Code: 
    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
Code: 
Len(Trim$(R.Text)) > 0
 
Upvote 0
Maybe

Code: 
LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
 
Upvote 0
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?
 
Upvote 0
Razor303 said:
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.
Click to expand...
To do this, try
Code: 
Range("A" & Evaluate("match(true," & Range("A:A").Address & "="""",0)")).Select
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Formula to find first blank cell and return another value
Replies
4
Views
46
Fluff
Fluff
B
  • Solved
Find last row of data in cell A that is a part of a table
Replies
7
Views
71
Alex Blakenburg
A
K
  • Question
Variable losing set Range
Replies
6
Views
60
Akuini
Akuini
F
  • Question
Find the first blank row
Replies
4
Views
188
NoSparks
N
L
  • Solved
Linking Data on Different Tab and Showing Blank Values Instead of Zeroes
Replies
3
Views
62
Fluff
Fluff

Forum statistics

Threads
1,215,092
Messages
6,123,063
Members
449,090
Latest member
fragment

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top