How to adjust or resize a range?

I have asked this question on other forums, but have not got a reply yet.
It is really simple question in excel, but struggled on VBA. Please see attached 3 different tabs. The first one is the origianal tab, second 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 thought 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 corner and delete any blank columns and rows. I am very keen to undertstand the Resize property in VBA. Lots of peopel use resize and offset in the middle of thier code, so would apprciate if some one could either correct my code,or come up with a new code which has offset and resize in it.

Thanks

Originally Posted by E-on

I have asked this question on other forums, but have not got a reply yet.
It is really simple question in excel, but struggled on VBA. Please see attached 3 different tabs. The first one is the origianal tab, second 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 thought 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 corner and delete any blank columns and rows. I am very keen to undertstand the Resize property in VBA. Lots of peopel use resize and offset in the middle of thier code, so would apprciate if some one could either correct my code,or come up with a new code which has offset and resize in it.

Thanks

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

I was unable to attach an excel file

On 5/07/2012 7:52 AM, E-on wrote:
> I was unable to attach an excel file
>
>
>
>
You have to upload it to a File-Sharing site like Excel Banter or similar.