Remove columns where all cell except header is null

Question

Due to organization restriction not able to share the workflow. I have a trial balance for 10 legal entites spread across 5 countries. I am generating reports based on the country names. In each report there are various tabs. In one tab i am creating a pivot using cross tab where i need the sum at legal entity level however my raw daya contains all 10 entity hence when i am generating the reports using render tool in country one report i am getting all 10 column where 8 column dont have any value rather then header. Not sure a batch macro or what can help. Have tried using data cleansing, dynamic select no clue so far, appreciate if anyone can help.

Suraj2742 · Answer

Can you please help?

Suraj2742 · Answer

Thanks for your response, attached is a sample data where i have shared only relevant tab data for your reference. In the actual workflow i am generating 10 tabs for each location and creating different files for each location. in the example if you see in Spain locations table Nordics columns are also coming where other then header all cell are blank and i want to have only columns relevant for Spian. The way i want to see the output is like below:-

test.yxwz

griffinwelsh · Answer

Your thinking sounds correct. Dynamic select and data cleansing are your best options to use for this within a batch macro. Are you able to provide sample data (just the headers and some fake values)? Without anything data to work with it is difficult to give you a precise solution.