VBA to copy a dynamic range to clipboard

[Purplemuffin]

Hi.

I'm trying and failing to get my head around VBA so thought I'd ask here.

I'm trying to write some VBA to attach to a button so that the user can copy a specific range of cells to clipboard in order to paste elsewhere.

This is the basic of my code:

Sub Copy_Test()
Range("A36:F39").Copy
End Sub

However - where it says F39, this could be anywhere between F38 and F50, where the row is taken to be the last non blank row (it would also have TOTAL in column E if that means the code is easier to write?)

Any ideas anybody please?

 

[Skyybot]

No - I just want to copy the cells that have something in them. These are cells that automatically populate from other data but could be anywhere from 3 rows to about 30 rows tall. All cells will have formula in though, in the form =IF(A3<>"", A3, "") (or variants on this)

Ok. I will most definitely need to see a copy of the Sheet for this. However, removing Blanks will change the structure of the Range. So, if you have categorized Columns, the data may be moved from one Column to another.