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

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)

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)

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

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

On Nov 9, 9:55*am, Claus Busch wrote:
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

Wow! That seems to work!
What is the best way to widen the range copied? also, to paste, here
is what i did: Is there a better way?

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

Worksheets("Sheet2").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

This works, just need to widen the selection. Many Thanks!

*** Do you know if that can be used with naming dynamic ranges? Those
are something i use a lot of, but have the same type issue. If there
are 'empty looking' cells with formulas in column A, below what i want
included in the range, it includes all those as well. So, its the
same situation.
Here is the canned formula i always use for dynamic ranges. to modify
this to work the same as the range copy solution you provided would be
fabulous!

=OFFSET('SheetX'!$A$1,0,0,COUNTA('SheetX'!$A:$A),3 7)

Thanks Again!!
-Steve

Hi Steve,

Am Wed, 9 Nov 2011 07:40:28 -0800 (PST) schrieb SS:

What is the best way to widen the range copied? also, to paste, here
is what i did: Is there a better way?

you want to copy 37 columns?
Try:
Dim myCount As Long
myCount = Columns("A:A").SpecialCells(xlCellTypeFormulas, 1).Cells.Count
Range("A1").Resize(myCount, 37).Copy
Sheets("Sheet2").Range("A1").PasteSpecial xlPasteValues


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

On Nov 9, 11:00*am, Claus Busch wrote:
Hi Steve,

Am Wed, 9 Nov 2011 07:40:28 -0800 (PST) schrieb SS:

What is the best way to widen the range copied? *also, to paste, here
is what i did: * Is there a better way?

you want to copy 37 columns?
Try:
Dim myCount As Long
myCount = Columns("A:A").SpecialCells(xlCellTypeFormulas, 1).Cells.Count
Range("A1").Resize(myCount, 37).Copy
Sheets("Sheet2").Range("A1").PasteSpecial xlPasteValues

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

This solution works Great, many thanks!!

My number of columns is always variable. I usually add custom parsing
formulas in columns to the right; never know how many.

Any thoughts on naming a range in this manner, please let me know (Not
VBA, but just in the Name Manager) . I like to use named ranges
for pivot table ranges so i never have to change the range
parameters.
Yes, the formula i posted is to name a dynamic range 37 columns wide.
Column A cannot have formulas going to 'forever', but the other 36
columns can.
I'd like to have formulas going down 'forever' in column A as well,
but it is the same issue. It sees the cells below the data with
formulas in them and includes those.

Again, Thanks to ALL for the help!!
-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

On Nov 9, 11:21*am, Gord Dibben wrote:
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- Hide quoted text -

- Show quoted text -

Are you kidding, apologizing... These solutions are unbelievable.
Thanks Very Much!! Works like a Charm!!

-Steve