Need VBA code to dtermine how many rows have data in them

  • Thread starter Thread starter Shani
  • Start date Start date
S

Shani

I need to determine how many rows contain data so that i know when to
stop a loop.

Also is there an or statement in VBA?

for example

If Cells(Rowx, Coly) Like "*cats*" or Cells(Rowx, Coly) Like "*dogs*"
The
....

I am new to VBA and truly appreciate your help.
 
Here is a good way.

dim i as integer
dim x as integer

x = ActiveSheet.UsedRange.Rows.Count

for i=1 to x
'insert loop code here
next i

or, if you wanted to select the next available free cell in a column,
you could use

x = ActiveSheet.UsedRange.Rows.Count
ActiveCell.SpecialCells(xlLastCell).Select
ActiveCell.EntireRow.Cells(1, 1).Offset(1, 0).Select

This would select the first cell in column 1 that was free right below
the last used row.
Obviously, you can alter cells(x,x) or the offset(x,x) to fit your
needs.

joran6
 
A convenient way to find rows in a list of data is the .CurrentRegion
property, e.g. Range("A1").CurrentRegion.Rows.Count gives the number of rows
in the rectangular region including all contiguous used cells starting in
cell A1.

For the "or", yes there is a VBA way to do it and it is straightforward: in
fact, you have it correct!
 
Back
Top