Variable Range Copy...

  • Thread starter Thread starter SS
  • Start date Start date
S

SS

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








- Show quoted text -
Click to expand...

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






- Show quoted text -
Click to expand...

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 paste>special>values 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
 
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)
Click to expand...

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.
Click to expand...

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


Regards
Claus Busch
 
Hi Steve,

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


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

Regards
Claus Busch
Click to expand...

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),37)

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?
Click to expand...

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
 
Hi Steve,

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


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
Click to expand...

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




- Show quoted text -
Click to expand...

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

-Steve
 
Back
Top