counts rows in data

[Guest]

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

 

[Norman Jones]

Hi Bryan

Dim i as long

For i = 1 to Selection.Rows.Count

'Your code

Next i

 

[Guest]

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]

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
'<<===============

 

[Gary Keramidas]

or could you loop through the range?

Range("test").Address

 

[Bob Phillips]

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)

 

[Gord Dibben]

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

 

[Mike Fogleman]

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

Mike F