Select a range not equal to:

  • Thread starter Thread starter Pete
  • Start date Start date
P

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
 
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
Click to expand...

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
 
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
 
Back
Top