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

counts rows in data
 

Hi Bryan

Dim i as long

For i = 1 to Selection.Rows.Count

'Your code

Next i


---
Regards,
Norman



"Bryan Brassell" wrote in message
...
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

counts rows in data
 

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" wrote in message
...
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

counts rows in data
 

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" wrote in message
...
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" wrote in
message
...
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

counts rows in data
 

or could you loop through the range?

Range("test").Address

--


Gary


"Bryan Brassell" wrote in message
...
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" wrote in
message
...
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

counts rows in data
 

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" wrote in message
...
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" wrote in
message
...
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

counts rows in data
 

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"
wrote:

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

counts rows in data
 

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

Mike F
"Bryan Brassell" wrote in message
...
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