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.

    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


    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.


  • Re: Stopping Loop on empty cell

    Well, I figured it out myself.

    This is what I did:

    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.

    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.