Stopping Loop on empty cell

droye · May 4th 2005

Good day,

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

Code

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
    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

**norie** · May 4th 2005

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.

droye · May 4th 2005

Re: Stopping Loop on empty cell

Hi, Norrie -

I'm just learning VBA, could you give me an example?

droye · May 5th 2005

Re: Stopping Loop on empty cell

Well, I figured it out myself.

This is what I did:

Code

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

and it works.

s_u_resh · Aug 5th 2011

Re: Stopping Loop on empty cell

you can use for loop is much better.

Code

for i = 4 to range("A65536").end(xlup).row


Range("A"& i).value = value to pass


next

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

hope this helps you.

Suresh

**Reafidy** · Aug 5th 2011

Re: Stopping Loop on empty cell

Thanks s_u_resh, in future please use code tags for ALL code.

Stopping Loop on empty cell

Participate now!

Share