Results 1 to 2 of 2

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

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

    Default Loop until empty

    Hello,

    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
    Location
    Netherlands
    Posts
    3,463
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    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:

    Code:
    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
        Loop
    End Sub
    Last edited by sijpie; Jul 22nd, 2019 at 05:59 AM.
    Short Guide to Better VBA - Link: https://www.mrexcel.com/forum/showthread.php?t=712119

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




    Engelse lessen, persoonlijk en doelgericht. Dutch tuition tailor-made for you. https://Ennef.nl

    Wearable for people with panic attacks: sidjup https://sidjup.com

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
  •