On Nov 9, 12:18*am, Gord Dibben wrote:
This looks better and resizes the copy range to 5 columns.

Sub Copy_Value_Cells()
* * Dim WksRng As Range
* * Dim vCells As Range
* * Dim Cell As Range
* * Dim copyrng As Range
* * Set WksRng = ActiveSheet.Range(Range("A1"), _
* * * * * * * * * * * * * * * * * *Cells(Rows.Count, 1).End(xlUp))
* * For Each Cell In WksRng
* * * * If Cell.Value < "" Then
* * * * * * If vCells Is Nothing Then
* * * * * * * * Set vCells = Cell
* * * * * * Else
* * * * * * * * Set vCells = Union(vCells, Cell)
* * * * * * End If
* * * * End If
* * Next Cell
* * If vCells Is Nothing Then
* * * * MsgBox "No Values in this range."
* * End If
* * Set copyrng = vCells.Resize(vCells.Rows.Count, 5)
* * copyrng.Copy Destination:=Sheets("Sheet2").Range("A1")
End Sub

Gord

On Tue, 8 Nov 2011 17:45:51 -0800 (PST), SS
wrote:



On Nov 8, 4:51*pm, Gord Dibben wrote:
Sub Copy_Value_Cells()
* * Dim WkskRange As Range
* * Dim vCells As Range
* * Dim Cell As Range
* * Set WksRng = ActiveSheet.Range(Range("A1"), _
* * * * * * Cells(Rows.Count, 1).End(xlUp))
* * For Each Cell In WksRng
* * * * If Cell.Value < "" Then
* * * * * * If vCells Is Nothing Then
* * * * * * * * Set vCells = Cell
* * * * * * Else
* * * * * * * * Set vCells = Union(vCells, Cell)
* * * * * * End If
* * * * End If
* * Next Cell
* * If vCells Is Nothing Then
* * * * MsgBox "No Values in this range."
* * Else
* * * * vCells.Copy Destination:=Sheets("Sheet2").Range("A1")
* * End If
End Sub

Gord

On Tue, 8 Nov 2011 12:22:23 -0800 (PST), SS
wrote:

Hi,
I am looking for a way to copy a variable range of values.

My spreadsheet has formulas in A1:A100. *If conditions are met, values
are displayed, otherwise the formulas return no value, or an empty
looking cell. *Of course the empty cells are not blank; they have a
formula.

So, say that A1:A100 have formulas. *But only A1:A50 have values
returned (Will always be A1 to ? (up to A100)).
I want to only select and copy A1:A50 (this could be different next
time; that's why it's variable) to another location.

The problem is the method i use always selects A1:A100, because is
sees the 'empty' cells with formulas and includes those as well.

How do i get it to only go the last cell in column A with a returned
value, not the last cell with a formula.

Many Thanks!!- Hide quoted text -

- Show quoted text -

Thanks for the response. *However, I can't get this to work unless
there are just values in the cells to copy, not formulas.
What i have is formulas in A1:A100. *The first 23 rows (A1:A23) are
returning the number 112. *But there is still a formula in those
cells.
Cells A24:A100 are returning a blank, or "".

On a side note... *I thought this would be easy to widen the range
copied 5 columns once i determined the end of the values, but seeing
your code, i may not be able to do that with a simple offset.

Thanks Again,
Steve- Hide quoted text -

- Show quoted text -

Ok... Very, Very Close. It is copying only the range i need, however,
it is pasting the formulas in the destination. I need the values
pasted.
I always do pastespecialvalues to paste a selection; but your copy/
paste code is totally different than that; and i'm sure way more
efficient :)
Is there a way to past the values in the destination, not the source
formulas?

Thanks Again!
-Steve