counts rows in data

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
Hi Bryan,
Thanks - only thing left, is how do I make the selection each time?
Click to expand...

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

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
 
I like Bob Phillips' second offering because it is easy to modify which
column you want the row count from.

Mike F
 
Back
Top