I recorded a macro based on downloaded data that will be of varying length whenever downloaded. The columns are always A-P, but the last line changes. When I recorded the macro and used the autofill, my macro recorded the exact range that existed in the report I used at that time.
To cover various length reports, I changed those autofills in the macro to go down to row 5000, and my pivot table to cover down to row 5000, which worked fine until I had to run a year-to-date report yesterday based on weekly data instead of the usual monthly data. When I finally realized that my table was missing data beyond line 5000, I changed my macro again, now covering all the way down to 61545, just in case.
This means I end up with spare bits of useless data at the end of my real data: a comma in column L, from concatenating, and 1, 1, 1900 in F, G & H from breaking out month, quarter & year.
Is there a simple way that I can change my row 61545 references in the macro to instead reference the final line of my data, whatever that may be in any given report?
Thank you,
To cover various length reports, I changed those autofills in the macro to go down to row 5000, and my pivot table to cover down to row 5000, which worked fine until I had to run a year-to-date report yesterday based on weekly data instead of the usual monthly data. When I finally realized that my table was missing data beyond line 5000, I changed my macro again, now covering all the way down to 61545, just in case.
This means I end up with spare bits of useless data at the end of my real data: a comma in column L, from concatenating, and 1, 1, 1900 in F, G & H from breaking out month, quarter & year.
Is there a simple way that I can change my row 61545 references in the macro to instead reference the final line of my data, whatever that may be in any given report?
Thank you,