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!!

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!!

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

Are you able to see my previous posting? If your formulas are returning
numbers (not text), which your answer to Gord seems to be indicating, then I
believe the macro I posted should work for you.

Rick Rothstein (MVP - Excel)

On Nov 8, 10:55*pm, "Rick Rothstein"
wrote:
Are you able to see my previous posting? If your formulas are returning
numbers (not text), which your answer to Gord seems to be indicating, then I
believe the macro I posted should work for you.

Rick Rothstein (MVP - Excel)

Yes, I did try that, but could not get it to work. Another situation:
Cell A1 has the formula =IF(ISNUMBER(B1),$D$2,""). this is copied
down to say A30.
Their are numbers in B1:B23, and the number 112 in D2; so the fomulas
return the number 112 in A1:A23.
I need to select only A1:A23 (but need to know how to manipulate the
code to go more columns wide if needed), copy, and paste to a location
i specify.

Actually, I could not get yours or Gord's code to work. What i did
was copy the code to my personal projects, assigned hot keys, created
a Sheet2, and tried running them while i was on the sheet that has the
data to be copied.
Neither worked. OF COURSE, I FIGURE ITS ME DOING SOMETHING WRONG...
AS THAT IS ALMOST ALWAYS THE CASE :) But, i tried several things and
all that worked was to manually type something in column A (anywhere
in the range, text or number) and it would work as expected; but of
course i have to have the formulas there, not straight text/number.
That is how Gord's worked, but i could not get a result, error or
otherwise, from your code. But, I still feel it's me :)

I just tried Gord's second solution. Please see the response to his
post. This solution is very, very close. I have actually needed this
solution for a long time, on many projects; as i can imagine maybe a
lot of other people.

Thanks Again!!
-Steve

Hi Steve,

Am Wed, 9 Nov 2011 06:44:11 -0800 (PST) schrieb SS:

Yes, I did try that, but could not get it to work. Another situation:
Cell A1 has the formula =IF(ISNUMBER(B1),$D$2,""). this is copied
down to say A30.
Their are numbers in B1:B23, and the number 112 in D2; so the fomulas
return the number 112 in A1:A23.
I need to select only A1:A23 (but need to know how to manipulate the
code to go more columns wide if needed), copy, and paste to a location
i specify.

try:
Columns("A:A").SpecialCells(xlCellTypeFormulas, 1).Copy


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

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

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

Apologies. I re-read original post and you did say "copy values".

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
Sheets("Sheet2").Cells(1, 1).PasteSpecial xlPasteValues
Application.CutCopyMode = False
End Sub


Gord


On Wed, 9 Nov 2011 06:37:31 -0800 (PST), SS
wrote:

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

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

If the values you speak of are numbers (not text), then you can use this
macro to do what you want...

Sub Copy_Value_Cells()
Dim LR As Long
LR = Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row
On Error Resume Next
Range("A1:A" & LR).SpecialCells(xlCellTypeFormulas, _
xlNumbers).Copy Sheets("Sheet2").Range("A1")
End Sub

Rick Rothstein (MVP - Excel)