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

On Wednesday, June 27, 2012 3:48:15 PM UTC-4, Pete wrote:
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

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 P54:P68. The results that actually return a value are P54:P64 in this instance. Wouild like to select that range. (P65:P68 return the "")

Maybe that would help?

Thanks.
Pete


On Thursday, June 28, 2012 11:50:04 AM UTC-5, James Ravenswood wrote:
On Wednesday, June 27, 2012 3:48:15 PM UTC-4, Pete wrote:
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

How about:


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

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

On Thursday, June 28, 2012 2:31:41 PM UTC-5, James Ravenswood wrote:
How about:


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