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


    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

Participate now!

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