counts rows in data

Forgot how to write the code for counting rows in a selection. I need to set
up a for next loop to work in the data range, but the number of rows in the
data range changes periodically.


--
Regards,

Bryan Brassell
Padgett Business Services
281-897-9141

Hi Bryan

Dim i as long

For i = 1 to Selection.Rows.Count

'Your code

Next i


---
Regards,
Norman



"Bryan Brassell" <[email protected]> wrote in message
news:[email protected]...
> Forgot how to write the code for counting rows in a selection. I need to
> set
> up a for next loop to work in the data range, but the number of rows in
> the
> data range changes periodically.
>
>
> --
> Regards,
>
> Bryan Brassell
> Padgett Business Services
> 281-897-9141

Thanks - only thing left, is how do I make the selection each time? Start in
cell a1 and use somthing like activerange, etc?
--
Regards,

Bryan Brassell
Padgett Business Services
281-897-9141


"Norman Jones" wrote:

> Hi Bryan
>
> Dim i as long
>
> For i = 1 to Selection.Rows.Count
>
> 'Your code
>
> Next i
>
>
> ---
> Regards,
> Norman
>
>
>
> "Bryan Brassell" <[email protected]> wrote in message
> news:[email protected]...
> > Forgot how to write the code for counting rows in a selection. I need to
> > set
> > up a for next loop to work in the data range, but the number of rows in
> > the
> > data range changes periodically.
> >
> >
> > --
> > Regards,
> >
> > Bryan Brassell
> > Padgett Business Services
> > 281-897-9141
>
>
>

Hi Bryan,

> Thanks - only thing left, is how do I make the selection each time?

Ideally, you do not! It is rarely necessary, and is usually inefficient, to
make selections. A preferable approach would be to set the range to an
object variable and manipulate the variable.

For example:

'===============>>
Sub TestIt()
Dim rng As Range
Dim rCell As Range

Set rng = Selection

For Each rCell In rng.Cells
If rCell.Value > 100 Then
'do something, e.g.;
rCell.Interior.ColorIndex = 6
Else
'Do something else, e.g.:
rCell.Value = rCell.Value * 2
End If
Next rCell

End Sub
'<<===============

---
Regards,
Norman



"Bryan Brassell" <[email protected]> wrote in message
news:[email protected]...
> Thanks - only thing left, is how do I make the selection each time? Start
> in
> cell a1 and use somthing like activerange, etc?
> --
> Regards,
>
> Bryan Brassell
> Padgett Business Services
> 281-897-9141
>
>
> "Norman Jones" wrote:
>
>> Hi Bryan
>>
>> Dim i as long
>>
>> For i = 1 to Selection.Rows.Count
>>
>> 'Your code
>>
>> Next i
>>
>>
>> ---
>> Regards,
>> Norman
>>
>>
>>
>> "Bryan Brassell" <[email protected]> wrote in
>> message
>> news:[email protected]...
>> > Forgot how to write the code for counting rows in a selection. I need
>> > to
>> > set
>> > up a for next loop to work in the data range, but the number of rows in
>> > the
>> > data range changes periodically.
>> >
>> >
>> > --
>> > Regards,
>> >
>> > Bryan Brassell
>> > Padgett Business Services
>> > 281-897-9141
>>
>>
>>

or could you loop through the range?

Range("test").Address

--


Gary


"Bryan Brassell" <[email protected]> wrote in message
news:[email protected]...
> Thanks - only thing left, is how do I make the selection each time? Start
> in
> cell a1 and use somthing like activerange, etc?
> --
> Regards,
>
> Bryan Brassell
> Padgett Business Services
> 281-897-9141
>
>
> "Norman Jones" wrote:
>
>> Hi Bryan
>>
>> Dim i as long
>>
>> For i = 1 to Selection.Rows.Count
>>
>> 'Your code
>>
>> Next i
>>
>>
>> ---
>> Regards,
>> Norman
>>
>>
>>
>> "Bryan Brassell" <[email protected]> wrote in
>> message
>> news:[email protected]...
>> > Forgot how to write the code for counting rows in a selection. I need
>> > to
>> > set
>> > up a for next loop to work in the data range, but the number of rows in
>> > the
>> > data range changes periodically.
>> >
>> >
>> > --
>> > Regards,
>> >
>> > Bryan Brassell
>> > Padgett Business Services
>> > 281-897-9141
>>
>>
>>

Either

For i = 1 to ACtivesheet.UsedRange.Rows.Count

or

For i = 1 To Cells(Rows.Count,"A").End(xlUp).Row

--

HTH

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


"Bryan Brassell" <[email protected]> wrote in message
news:[email protected]...
> Thanks - only thing left, is how do I make the selection each time? Start
in
> cell a1 and use somthing like activerange, etc?
> --
> Regards,
>
> Bryan Brassell
> Padgett Business Services
> 281-897-9141
>
>
> "Norman Jones" wrote:
>
> > Hi Bryan
> >
> > Dim i as long
> >
> > For i = 1 to Selection.Rows.Count
> >
> > 'Your code
> >
> > Next i
> >
> >
> > ---
> > Regards,
> > Norman
> >
> >
> >
> > "Bryan Brassell" <[email protected]> wrote in
message
> > news:[email protected]...
> > > Forgot how to write the code for counting rows in a selection. I need
to
> > > set
> > > up a for next loop to work in the data range, but the number of rows
in
> > > the
> > > data range changes periodically.
> > >
> > >
> > > --
> > > Regards,
> > >
> > > Bryan Brassell
> > > Padgett Business Services
> > > 281-897-9141
> >
> >
> >

Bryan

Range(ActiveCell, Cells(Rows.Count, ActiveCell.Column).End(xlUp)).Select

OR No selection.......

Set Rng = Range(ActiveCell, Cells(Rows.Count, ActiveCell.Column).End(xlUp))

With Rng
'do what you want
End With


Gord Dibben Excel MVP

On Fri, 2 Dec 2005 12:43:03 -0800, "Bryan Brassell"
<[email protected]> wrote:

>Thanks - only thing left, is how do I make the selection each time? Start in
>cell a1 and use somthing like activerange, etc?

I like Bob Phillips' second offering because it is easy to modify which
column you want the row count from.

Mike F
"Bryan Brassell" <[email protected]> wrote in message
news:[email protected]...
> Forgot how to write the code for counting rows in a selection. I need to
> set
> up a for next loop to work in the data range, but the number of rows in
> the
> data range changes periodically.
>
>
> --
> Regards,
>
> Bryan Brassell
> Padgett Business Services
> 281-897-9141