2 part question:
1. I’ve got a comma delimited text file I work with weekly, which I pull into excel and chart. Being minimally conversant with VBA, I’ve managed to record and tweak macros for most aspects of this, but there are 4 columns that regularly come down with extra spaces at the end, which stuffs up the charting. Manually, I insert a column, put a trim formula in the blank one then paste values back into the original, deleting the column, but I’d like to automate this too. Does anyone have a subroutine that will eliminate trailing spaces from all filled cells (the range varies from week to week, so I can’t just record this process, since if I drag the formula down too far it leaves ‘values’ which extend the table out and throw the charts off.
2nd part of the question: How do I get a macro to prompt for an entry which will then be used to name the spreadsheet, ie Stats Week 52? If it’s possible I’d like just to be prompted for the week number, and the rest stay constant.
Thanks,
Crystal