Macro to iterate through columns and rows irrespective of the number of columns and rows.

[atk]

Hi Team, 

I'm trying to create a macro to iterate through every column and every row in the input data and anonymises the data in every cell. But with my current workflow (although the anonymisation works), the macro only iterates through the the number of columns and rows that it was initially fed with, while creating it (macro). I'd ideally like the macro to iterate through every column and row that it has been inputted irrespective of the number of columns and rows. 

I've read through a lot of blogs on the community and couldn't quite find the exact solution to my problem.

Any help from your end would be appreciated!

Looking forward to your response. :)

Regards,

Aishwarya

[IraWatt · Accepted solution]

Hey @atk,

What logic are you using to anonymise the data? Would a multi field formula not work? it can loop through each row and column dynamically and can apply anonymisation logic?

All the best,

Ira

[Qiu · Accepted solution]

@atk 
I think you might to check the the combination of Transpose and Cross Tab.

Transpose will transform the data into a vertical form, then go through your Macro, after than use Cross Tab to transform the data back to their original form.

If you can provide a sample input, we will be happy to make a demostration flow.

[atk]

Thank you Ira- worked for me. :)

[IraWatt]

No worries @atk ! glad it worked for you :)

[Shifty]

Nice, @IraWatt.  That will come in handy!