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]

Sub CopyRange()
Dim wb As Workbook, sht As Worksheet, rng As Range, lRow As Long, lCol As Long
Set wb = ThisWorkbook: Set sht = wb.ActiveSheet
lRow = sht.Rows(sht.Rows.Count).End(xlUp).Row
lCol = sht.Columns(sht.Columns.Count).End(xlToLeft).Column
Set rng = sht.Range("A36", Cells(lRow, "F"))
rng.Copy
End Sub

 

[Tom Urtis]

Maybe you want to copy everything onto your clipboard except the last used row, if so,
With Range("A36").CurrentRegion
.Resize(.Rows.Count - 1, .Columns.Count).Copy
End With

Maybe you want to copy just the values (no formatting) except the last used row and send elsewhere, if so:
With Range("A36").CurrentRegion
DestinationRange.Value - .Resize(.Rows.Count - 1, .Columns.Count).Value
End With

The latter requires info from you about where the destination shall be.

 

[Skyybot]

Let me know if Post #11 works.

 

[Purplemuffin]

Post #11 did much the same as Post #2 by the looks of it.

I think what it is, is that there are formulae in cells A37:F50 so even though it looks blank to the eye, it's actually got something in there.

Is there a workaround for this? Should I rewrite my formula somehow so that rather than it displaying a blank via "", it displays as a true empty cell?

 

[Skyybot]

Can you post a copy of your Worksheet via XL2BB?

 

[Skyybot]

I think what it is, is that there are formulae in cells A37:F50 so even though it looks blank to the eye, it's actually got something in there.

The code should find the last Non-Blank cell. Even if there is a Formula that is outputting "", it will see it.

 

[Skyybot]

I think I found the problem. Try this:

Sub CopyRange()
Dim wb As Workbook, sht As Worksheet, rng As Range, lRow As Long, lCol As Long
Set wb = ThisWorkbook: Set sht = wb.ActiveSheet
lRow = sht.Columns("F").Rows(sht.Rows.Count).End(xlUp).Row
Set rng = sht.Range("A36", Cells(lRow, "F"))
rng.Copy
End Sub

 

[Purplemuffin]

I think I found the problem. Try this:

Sub CopyRange()
Dim wb As Workbook, sht As Worksheet, rng As Range, lRow As Long, lCol As Long
Set wb = ThisWorkbook: Set sht = wb.ActiveSheet
lRow = sht.Columns("F").Rows(sht.Rows.Count).End(xlUp).Row
Set rng = sht.Range("A36", Cells(lRow, "F"))
rng.Copy
End Sub

No I'm sorry that's still grabbed everything including the cells that are blank to the eye (but not truly blank).

 

[Skyybot]

Oh. You don't want to copy Blank cells?

 

[Purplemuffin]

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)