HELP!! NEED TO SELECT DYNAMIC RANGE? !!!!!!!
Posted by mike on July 18, 2000 12:16 PM
I have to select a range from cell A1 to the first cell with the value =70 not including it. I want to then put this into a pivot table.
THANKS@
Posted by Ryan on July 18, 0100 1:00 PM
Mike,
Are the values in column A?
Ryan
Posted by mike on July 18, 0100 1:15 PM
yes the values are in column a
Posted by Ryan on July 18, 0100 1:19 PM
Mike,
This will do the trick. You'll have to work it into your code but this is a basic way to find "70". Hope it helps!
Ryan
Sub SelectRangeto70()
Dim x As Long
For x = 1 To Range("A6500").End(xlUp).Row
If Cells(x, 1).Value = 70 Then Exit For
Next x
Range("A1:A" & x - 1).Select
End Sub
Posted by MIKE on July 18, 0100 1:28 PM
how do i put that into a pivot table??
Posted by Ryan on July 18, 0100 1:31 PM
Beats me :)! I'd have to see the rest of your code. If you want to send me your workbook I can take a quick glance at it.
ufexcel@hotmail.com
Ryan
Posted by Ada on July 18, 0100 10:48 PM
Dynamic named range without VBA
Mike
I don't know if this will be of any use, but you can put the formula below in the RefersTo box.
It should give you the dynamuc range that you need.
The formula returns the range address (eg. A1:A15).
If your data exceeds 1000 rows, just increase the rows in the formula.
=IF(ISERROR(ADDRESS(1,1)&":"&ADDRESS(SMALL(IF($A$1:$A$1000=70,ROW($A$1:$A$1000)-1),1),1)),"No cell has 70",ADDRESS(1,1)&":"&ADDRESS(SMALL(IF($A$1:$A$1000=70,ROW($A$1:$A$1000)-1),1),1))
If you want to enter the formula directly into a worksheet cell, it is an array formula, so enter by Ctrl+Shift+Enter.
Ada
