Results 1 to 2 of 2

Thread: Loop until empty
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    May 2018
    Post Thanks / Like
    0 Post(s)
    0 Thread(s)

    Default Loop until empty


    I have a list in column A in an Excel workbook called "Pipe Sizes".

    I want workbook "template" to read A2 and assign "job no" to value in A2. Run "start" macro. At end of "start" macro, assign "job no" to value in A3 and run "start" macro.

    So on so forth until it hits an empty cell.

    How could I do this?

  2. #2
    Board Regular
    Join Date
    Nov 2008
    Post Thanks / Like
    0 Post(s)
    1 Thread(s)

    Default Re: Loop until empty

    There are several ways to do this. It depends a little bit on how your Start macor is written. If it assumes that it works on the 'Activecell', then you need to select each cell in turn. It will work that way, no problem, but if you have thousands of rows, then selecting each cell will slow down the macro.

    A quicker way would then be to pass each cell address to the start macro. If there are only say a hundred rows then in reality the first method is fine. Here goes:

    Sub LoopThroughColA()
        Dim rC As Range
        Set rC = Range("A2")         'set to first cell in range
        Do While Len(rC)               'do while there are contents in cell
            rC.Select                       'make this cell active
            Start                             'run the start macro
            Set rC = rC.Offset(1, 0)  'set the cell one row down
    End Sub
    Last edited by sijpie; Jul 22nd, 2019 at 06:59 AM.
    Short Guide to Better VBA - Link:

    Please use code tags around your code:
    [Code] Your code here... [/Code]

    Engelse lessen, persoonlijk en doelgericht. Dutch tuition tailor-made for you.

    Wearable for people with panic attacks: sidjup

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts