How to adjust or resize a range?

[E-on]

I have asked this question on other forums, but have not got a repl
yet.
It is really simple question in excel, but struggled on VBA. Please se
attached 3 different tabs. The first one is the origianal tab, secon
one is what I am looking for as a solution and third one is my attempt.

In excel, I can cut, move and copy in to a desired range, and I though
using a combination of offset and resize will do the same job in VBA
All I wanted is using VBA to move the range in to top left hand corne
and delete any blank columns and rows. I am very keen to undertstand th
Resize property in VBA. Lots of peopel use resize and offset in th
middle of thier code, so would apprciate if some one could eithe
correct my code,or come up with a new code which has offset and resiz
in it.

Thank

 

[Spencer101]

I have asked this question on other forums, but have not got a repl
yet.
It is really simple question in excel, but struggled on VBA. Please se
attached 3 different tabs. The first one is the origianal tab, secon
one is what I am looking for as a solution and third one is my attempt.

In excel, I can cut, move and copy in to a desired range, and I though
using a combination of offset and resize will do the same job in VBA
All I wanted is using VBA to move the range in to top left hand corne
and delete any blank columns and rows. I am very keen to undertstand th
Resize property in VBA. Lots of peopel use resize and offset in th
middle of thier code, so would apprciate if some one could eithe
correct my code,or come up with a new code which has offset and resiz
in it.

Thanks

Just a thought, but it might help if you attach the example workbook...
;

 

[E-on]

I was unable to attach an excel fil

 

[Vacuum Sealed]

I was unable to attach an excel file

You have to upload it to a File-Sharing site like Excel Banter or similar.