Hi

My problem is similar (but not identical to) another recently posted problem



I'm trying to set up a macro that will allow me to select a range of data
cells dynamically.
The length of the range will vary as shown below:



Val1 10

Val2 20

Val3 50

0 0

0 0

0 0



If I use Rang(A1:B6), all of the above data will be selected, but I'm not
interested in the 0 values. The next time I may have the following results:



Val1 10

Val2 20

Val3 50

Val4 10

0 0

0 0



i.e additional data



I can use the countif function to give me the number of rows which are not
zero, but I cannot combine the above functions (range and countif).

Any ideas?



Thanks,



Sara

Maybe

Dim i As Long

For i = Cells(Rows.Count,"A").End(xlUp).Row
If Cells(i,"A").Value < 0 Then
Exit For
End If
Next i
Range("A1:A" & i).Select

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Alex Nayar" wrote in message
...
Hi

My problem is similar (but not identical to) another recently posted
problem



I'm trying to set up a macro that will allow me to select a range of data
cells dynamically.
The length of the range will vary as shown below:



Val1 10

Val2 20

Val3 50

0 0

0 0

0 0



If I use Rang(A1:B6), all of the above data will be selected, but I'm not
interested in the 0 values. The next time I may have the following
results:



Val1 10

Val2 20

Val3 50

Val4 10

0 0

0 0



i.e additional data



I can use the countif function to give me the number of rows which are not
zero, but I cannot combine the above functions (range and countif).

Any ideas?



Thanks,



Sara

set rng = Range(A1").Resize(application.Countif(Range("B:B") ,"<0"),2)
rng.Select

--
Regards,
Tom Ogilvy


"Alex Nayar" wrote in message
...
Hi

My problem is similar (but not identical to) another recently posted
problem



I'm trying to set up a macro that will allow me to select a range of data
cells dynamically.
The length of the range will vary as shown below:



Val1 10

Val2 20

Val3 50

0 0

0 0

0 0



If I use Rang(A1:B6), all of the above data will be selected, but I'm not
interested in the 0 values. The next time I may have the following
results:



Val1 10

Val2 20

Val3 50

Val4 10

0 0

0 0



i.e additional data



I can use the countif function to give me the number of rows which are not
zero, but I cannot combine the above functions (range and countif).

Any ideas?



Thanks,



Sara

You need to create an indirect reference from your countif function. It will
look similar to: Indirect(concatenate("A1:B",COUNTIF(B1:B2000,"0") ))

"Alex Nayar" wrote:

If I use Range(A1:B6), all of the above data will be selected, but I'm not
interested in the 0 values. The next time I may have the following results:



Val1 10

Val2 20

Val3 50

Val4 10

0 0

0 0



i.e additional data



I can use the countif function to give me the number of rows which are not
zero, but I cannot combine the above functions (range and countif).

Any ideas?



Thanks,



Sara

Tom,

I've just got to jump in and say that's a nice bit of code!

Doug

"Tom Ogilvy" wrote in message
...
set rng = Range(A1").Resize(application.Countif(Range("B:B") ,"<0"),2)
rng.Select

--
Regards,
Tom Ogilvy


"Alex Nayar" wrote in message
...
Hi

My problem is similar (but not identical to) another recently posted
problem



I'm trying to set up a macro that will allow me to select a range of
data
cells dynamically.
The length of the range will vary as shown below:



Val1 10

Val2 20

Val3 50

0 0

0 0

0 0



If I use Rang(A1:B6), all of the above data will be selected, but I'm
not
interested in the 0 values. The next time I may have the following
results:



Val1 10

Val2 20

Val3 50

Val4 10

0 0

0 0



i.e additional data



I can use the countif function to give me the number of rows which are
not
zero, but I cannot combine the above functions (range and countif).

Any ideas?



Thanks,



Sara

Thanks. Just hope it works/solves the problem.

--
Regards,
Tom Ogilvy

"Doug Glancy" wrote in message
...
Tom,

I've just got to jump in and say that's a nice bit of code!

Doug

"Tom Ogilvy" wrote in message
...
set rng = Range(A1").Resize(application.Countif(Range("B:B") ,"<0"),2)
rng.Select

--
Regards,
Tom Ogilvy


"Alex Nayar" wrote in message
...
Hi

My problem is similar (but not identical to) another recently posted
problem



I'm trying to set up a macro that will allow me to select a range of
data
cells dynamically.
The length of the range will vary as shown below:



Val1 10

Val2 20

Val3 50

0 0

0 0

0 0



If I use Rang(A1:B6), all of the above data will be selected, but I'm
not
interested in the 0 values. The next time I may have the following
results:



Val1 10

Val2 20

Val3 50

Val4 10

0 0

0 0



i.e additional data



I can use the countif function to give me the number of rows which are
not
zero, but I cannot combine the above functions (range and countif).

Any ideas?



Thanks,



Sara