# Doiing a loop based on the number of non-empty rows

• I would like to do a loop equal to the number of rows that is not empty in my workbook.

What the code does is it looks for a value in the second column of the workbook and if it exits, it copies the whole row and pastes it onto another workbook, row by row.

My problem is that my original workbook may be anything from 25 to 2000 rows long but I do not want my loop to execute 2000 times each time it runs.
How can I count the number of nonempty rows and only let my loop execute that number of times.

This is my first post. Any help would be hugely appreciated

F

• Re: Doiing a loop based on the number of non-empty rows

Hi

You can use the counta function to aset up ur loop

see the example below

Code
``````Sub blankrow()
Dim myrange As Range
Set myrange = Sheets("sheet1").Range("A1:A100")
w = WorksheetFunction.CountA(myrange)
For i = 1 To w
'here you may place your code
Next i
End Sub``````

HTH

pangolin

HTH

• Re: Doiing a loop based on the number of non-empty rows

You have just to consider that if your "not empty" rows are not "grouped", your loop will stop somewhere in the range, doing only a part of the job.

filo65

P.S.

you maybe could add a line within pangolin's for loop, like: if row is empty reduce counter by 1 unit.

• Re: Doiing a loop based on the number of non-empty rows

Thanx alot.

• Re: Doiing a loop based on the number of non-empty rows

Would it not be better to find the last row that has something entered into it then loop through all the rows above? You can then loop through all these rows and if nothing is entered in a particular row, dont copy that row etc. If this is the case then you require something like this:

If you require any help on this just let me know.

Hope that helps

Matt B

• Re: Doiing a loop based on the number of non-empty rows

Thanx Matt

This works even better

F

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!