Variable Range Copy...

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

 

[Gord Dibben]

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

 

[Rick Rothstein]

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)

 

[SS]

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 -

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

 

[Rick Rothstein]

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)

 

[Gord Dibben]

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

 

[SS]

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 -

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

 

[SS]

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

 

[Claus Busch]

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

 

[SS]

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

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

 

[Claus Busch]

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

 

[SS]

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

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

 

[Gord Dibben]

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

 

[SS]

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 -

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

-Steve