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.

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

    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:

    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.

    for i = 4 to range("A65536").end(xlup).row
    Range("A"& i).value = value to pass

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

    hope this helps you.


Participate now!

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