+ Reply to Thread
Results 1 to 14 of 14

Macro that stops at last row of value

  1. #1
    Registered User
    Join Date
    04-21-2022
    Location
    Orlando, FL
    MS-Off Ver
    Office 365
    Posts
    11

    Macro that stops at last row of value

    I can only do some basic macro work, so now I'm stuck. I am attempting to copy a formula all the way down the sheet to the last row of data. Some days the data could go down 600 rows and another day only 220. How do I make the macro stop so I am not ending with removing bad info?
    Attached Files Attached Files
    Last edited by excel1jennifer0; 05-05-2023 at 09:16 AM.

  2. #2
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Macro that stops at last row of value

    You can make the range dynamic by using something like this:
    Please Login or Register  to view this content.
    It will find the last populated row in column A and copy the selected cell content only down to that row. It could be based off a different column but I've used A in this example for simplicity.
    There are also other ways of accomplishing the same and which you use will be down to personal preference and/or the process in hand.

    Is the Excel file you attached what your data looks like before or after running your code? Reason I ask is it's obviously a recorded macro and therefore terrible as far as VBA goes. I was going to rewrite it for you to make it a bit easier to manage in future but if the attached isn't the data pre processing then I'd need to see what that looks like.

    BSB

  3. #3
    Registered User
    Join Date
    04-21-2022
    Location
    Orlando, FL
    MS-Off Ver
    Office 365
    Posts
    11

    Re: Macro that stops at last row of value

    Hello! Thanks for your assistance. I am adding both the raw version and the completed version. I appreciate your help.Attachment 828177Attachment 828178Attachment 828179
    Last edited by excel1jennifer0; 05-05-2023 at 09:40 AM.

  4. #4
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Macro that stops at last row of value

    So if you run your current code on the raw data you end up with the "as completed now" version, and the difference there is a load of blank rows with borders at the end of the data that you no longer want.
    Are there any other differences? Easier for you to tell me about them than me trawl through and compare row by row

    BSB

  5. #5
    Registered User
    Join Date
    04-21-2022
    Location
    Orlando, FL
    MS-Off Ver
    Office 365
    Posts
    11

    Re: Macro that stops at last row of value

    Sorry I did have a small informational line that I must have erased. So yes, once I run the macro I either get two errors: it doesn't fill the times in all the way (because the macro only runs to 350, although there may be 500 rows of data) and the borders for each row continue down whether there is data or not all the way to 650. I'd like it to adjust based on the rows with value instead of the basic 350. The only other items it doesn't do now is basic formatting stuff: expand the column to fit the txt, highlight parties of 10 in column F and sum it at the bottom. Those are all items I do after running the macro.

  6. #6
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Macro that stops at last row of value

    No problem at all. Leave it with me and I shall report back shortly.

    All column not hidden need fitting to width?
    Parties greater than 10 are highlighted yellow?

    BSB

  7. #7
    Registered User
    Join Date
    04-21-2022
    Location
    Orlando, FL
    MS-Off Ver
    Office 365
    Posts
    11

    Re: Macro that stops at last row of value

    Columns that are hidden can stay hidden, no fit needed. They can be deleted or hidden. Parties of 10 are highlighted in yellow, yes. I really appreciate your help. *runs to buy VBA course*

  8. #8
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Macro that stops at last row of value

    Haha! I should get myself on a VBA course too! Haven't bothered in nearly 20 years of writing code

    This took me a bit longer than planned as I was getting confused. There are hidden columns in your example output files that don't appear in your raw data file so I had to mess around with it a little.
    Hopefully it works but happy to amend further if it doesn't.

    Give the below a try and see if it does what you need. It certainly seems to work on the sample raw data you attached above.
    Please Login or Register  to view this content.
    If it does do what you need then I can add comments to it to explain what each line is doing, if that would be helpful. Probably a better learning tool with a real world example than any course you could go on...

    BSB
    Last edited by BadlySpelledBuoy; 05-05-2023 at 09:10 AM.

  9. #9
    Registered User
    Join Date
    04-21-2022
    Location
    Orlando, FL
    MS-Off Ver
    Office 365
    Posts
    11

    Re: Macro that stops at last row of value

    I received an error when running the macro. I am including the results once the macro ran and a screenshot of where the error occurred.

    Macro Error.pngExcel Results .xlsx

  10. #10
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Macro that stops at last row of value

    Very bizarre! It works fine for me.

    All that line is doing is converting the numbers formatted as text to actual numbers. You can delete that line and see if it works from there, but I think the part where it sums the number of PAX at the end will result in 0.

    I can look at other ways of doing that if the rest works.

    BSB
    Last edited by BadlySpelledBuoy; 05-05-2023 at 10:09 AM.

  11. #11
    Registered User
    Join Date
    04-21-2022
    Location
    Orlando, FL
    MS-Off Ver
    Office 365
    Posts
    11

    Re: Macro that stops at last row of value

    Okay after more diving into this mess, I'm finding there's another macro run before mine. From the original data file, this macro is ran and then I am attempting to run mine. Which is why yours isn't working for me as it doesn't know the earlier adjustments?
    From raw file (raw file, no changes attachment), macro below is run.
    This gives me the revised version (raw file after macro attachment).


    Code also attached. Raw File No Changes.xlsxRaw File Changes After Macro 1 (run_custom_macros).xlsxCustom Macro Code.docx

  12. #12
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Macro that stops at last row of value

    OK, so what's the next step? The code I provided needs amending so it churns out your expected output AFTER these other macros have been run?

    BSB

  13. #13
    Registered User
    Join Date
    04-21-2022
    Location
    Orlando, FL
    MS-Off Ver
    Office 365
    Posts
    11

    Re: Macro that stops at last row of value

    Correct, thank you. I need the file to run the "custom macro" file and then end at my desired goal sheet. The only problem with that one is that it won't continue/stop at the row with the last value and the small details (fitting, highlighting over 10). It would be best to have the custom macro and then a separate one for everything else. We run the custom macro on another sheet which doesn't need all of this extra stuff after. I hope that makes sense.

  14. #14
    Registered User
    Join Date
    04-21-2022
    Location
    Orlando, FL
    MS-Off Ver
    Office 365
    Posts
    11

    Re: Macro that stops at last row of value

    Hello again. I was wondering if you can still help me? I understand it's a time and effort on your side, if you cannot. Can you at least point me in the right direction to sort of learn on my own? Or attempt to learn? :P

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Refresh macro stops working when combined with another macro
    By Spaaacce in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-26-2015, 04:06 PM
  2. Macro Stops Running
    By bhbjk1 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-02-2015, 08:22 PM
  3. Macro that runs when ran from Macro menu, but stops when using short cut keys...
    By Joven76 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 09-12-2014, 02:29 PM
  4. [SOLVED] Macro Stops at search
    By imogul in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 07-23-2013, 02:22 PM
  5. Execution of Macro Stops
    By dhatul in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 10-04-2010, 05:26 AM
  6. A dialog stops my macro
    By gtorres in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-08-2007, 10:00 AM
  7. My Macro stops
    By Bobby in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-23-2006, 03:10 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1