MrExcel Publishing
Your One Stop for Excel Tips & Solutions


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.


Posted by Ryan on July 18, 0100 1:00 PM


Are the values in column A?


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

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!


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.


Posted by Ada on July 18, 0100 10:48 PM

Dynamic named range without VBA

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.