Loop Through Rows Until Blank and Call Macro

  • Hi - I wonder if I can explain my problem?


    I have a spreadsheet with a list of names from cell A5 to A? - can be as many as 130 names or as few as 20.


    I have to create individual spreadsheets with the persons name as part of the filename. I have 3 subroutines which work manually ( CreateFile, LookUp and NewWorkbookSave ) so that I can generate individual files for those users who require them.
    However I need to be able on occasions to create a full teams worth of files with one click - to this end I need to be able to loop through cells A5 - A whatever and call the Subroutine CreateFile - I enclose the code I have so far - can anyone help please?


    Cheers
    George


  • Re: Loop Through Rows Until Blank and Call Macro


    Here's a simple loop macro for you:

    Regards,
    Simon

  • Re: Loop Through Rows Until Blank and Call Macro


    Your Lookup macro appears to serve no purpose. It sets a variable to equal the active cell and then does nothing. As the variable is declared within the Macro it is not used by any other macro.


    Try this macro for looping through the range of used cells a5 to last used row in column A


  • Re: Loop Through Rows Until Blank and Call Macro


    Thanks for the response guys
    Simon - yours works but I don't know why - which is all down to my ignorance and not your coding!


    that is why i have decided to go along with Mudrakers solution. ( BTW you were right about the Lookup macro - it was a legacy from something else I was testing )


    Once again thank you - what would I do without this forum?


    George

  • Re: Loop Through Rows Until Blank and Call Macro


    Thanks for the feed back


    Simons code is basically the same as mine - just structured slightly different


    My code checks a5 to last used row in column A
    Simons checks a5 to a200


    Both these codes are designed to loop through a range of cells - the setting of the range cells is slightly different. Simon set his befor the For command & I set mine as part of the For command - see to parts highlighted in red


    Code
    1. Simons code
    2. [COLOR="Red"]Set Rng = Sheets("Sheet1").Range("A5:A200") [/COLOR]
    3. For Each MyCell In Rng
    4. My code
    5. iLastRow = Cells(Rows.Count, "a").End(xlUp).Row
    6. For Each Rng In [COLOR="red"]Range("a5:a" & iLastRow)[/COLOR]


    Both of these commands check if the cell is blank


    Code
    1. If Not Rng.Value = vbNullString Then
    2. If MyCell <> "" Then
  • Re: Loop Through Rows Until Blank and Call Macro


    Mudraker is being modest he has made his range Dynamic which i was too lazy to do

    Code
    1. iLastRow = Cells(Rows.Count, "a").End(xlUp).Row
    2. For Each Rng In Range("a5:a" & iLastRow)

    effectively this couple of lines ensures that the code will run no matter what size of list you have in column A, whereas mine will not include any items below row 200!


    Regards,
    Simon