Stopping Loop on empty cell

  • Good day,


    I'm using a simple loop to remove unwanted spaces from a list of imported data in a single column.


    Code
    1. Counter = 0
    2. Do Until Selection.Offset(Counter, 0).Row > 5000
    3. Cells.Find(What:="__,______", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
    4. :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    5. False, SearchFormat:=False).Activate
    6. Selection.Delete Shift:=xlUp
    7. Loop


    My question, how can I stop the loop by checking for an empty cell, instead of using an arbitrary range?
    I have about a dozen data files I need to process and each of them varies in size (file length). I would like to nest several of these loops to help me clean up the raw data.
    Thanks for your help,
    David Roye

  • Re: Stopping Loop on empty cell


    David


    There are various ways to deal with this.


    You could include a test for a blank cell or you could establish how many records you have before your loop and then use For Next loop.

    Boo!:yikes:

  • Re: Stopping Loop on empty cell


    Well, I figured it out myself.


    This is what I did:


    Code
    1. Loop Until ActiveCell.Value = "" And ActiveCell.Offset(1, 0).Value = ""


    and it works.

  • Re: Stopping Loop on empty cell


    you can use for loop is much better.


    Code
    1. for i = 4 to range("A65536").end(xlup).row
    2. Range("A"& i).value = value to pass
    3. next


    by doing this we can control the number of rows in excel.


    hope this helps you.

    Suresh