Select a range not equal to:

[Pete]

Want to use VBA to select and copy a range that includes only the cells not
returning: "".

The formulas in the cells/range evaluated either calculate and return a value, or return a "" (from the formula)
The cells returning "" are always at the bottom of the range, while the cells returning data are on top.

Just want to highlight the results so as to copy them into another application.

TIA for any ideas.

Pete

 

[James Ravenswood]

Want to use VBA to select and copy a range that includes only the cells not
returning: "".

The formulas in the cells/range evaluated either calculate and return a value, or return a "" (from the formula)
The cells returning "" are always at the bottom of the range, while the cells returning data are on top.

Just want to highlight the results so as to copy them into another application.

TIA for any ideas.

Pete

How about:

Sub NotBlanks()
Dim rA As Range, rSmaller As Range
Set rA = Range("A1:A100")
For L = 100 To 1 Step -1
If Cells(L, 1).Value <> "" Then
Exit For
End If
Next
Set rSmaller = Range("A1:A" & L)
MsgBox rSmaller.Address
End Sub

 

[Pete]

James: I appreciate you taking a stab at it.
What we're looking for it to return is the cells that are not ""

In this case, the range to evaluate is P5468. The results that actually return a value are P5464 in this instance. Wouild like to select that range. (P6568 return the "")

Maybe that would help?

Thanks.
Pete

 

[James Ravenswood]

How about:


Sub NotBlanks()
Dim rA As Range, rSmaller As Range
Set rA = Range("P5468")
For L = 68 To 54 Step -1
If Cells(L, "P").Value <> "" Then
Exit For
End If
Next
Set rSmaller = Range("P54" & L)
MsgBox rSmaller.Address
End Sub

 

[Pete]

Thanks once again James.

Can the macro select the range instead of identifying it? Don't need the message box (The range, however identified and displayed the correct cells.)

Pete