Using VBA to transform Layout

Hi, when i extract my data, the layout aint in a very friendly manner. Coz there empty space, and aso Column A has both Customer Name and Customer ID in it.

I cant do filter etc. Hence i wan to covert the data to a Table-Like way.

Anyway to use VBA to transform it??

My attached file has 2 tab. One is the raw, one is what i wan.

Hi Nankw, thanks for your code, how about if the Cust number is not necessary starting with #? Can be random Character, but then the Customer Name is always on top of the Cust number.

Hi Nankw83, Thanks for your code