I receive hundreds of messy worksheets from our customers each month. I need to convert this data to a list format for pivot table analysis and recording. Most contain merged cells, blank rows, subtotals and empty columns that I must remove. I am looking for some simple, flexible VBA code that will help me get a jump on each of these files.
Here's what I'm thinking:
Step1 Select entire sheet and remove merged cells
Step2 In the block of data, delete any COLS without headers in the first row
Step3 In the block of data, delete any COLS with headers but no data in ROW2
Step4 Delete any rows without data in the COL A
Attached is a slice of a typical example.
Thanks for your help, Experts!