selecting non-contiguous blocks of cells in vb
 

When I use the record macro from the drop down menu to select multiple
non-continguous blocks of cells to copy/paste, I get code like this:

Range("B13:B34,D13:D34,I13:I34").Select
Range("I13").Activate
Selection.Copy
Range("S13").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

I tried to duplicate the above in vb using this:

Dim fmcv As Worksheet
Dim topbot As Worksheet
' every other variable below undefined explicitly were defined as Long

fmcv.Activate
Range(Range(Cells(fmcvRowStart, fmcvSecidCol), Cells(fmcvRowEnd,
fmcvSecidCol)), _
Range(Cells(fmcvRowStart, fmcvIssuerCol), Cells(fmcvRowEnd,
fmcvIssuerCol)), _
Range(Cells(fmcvRowStart, fmcvPctPortfolioCol), Cells(fmcvRowEnd,
fmcvPctPortfolioCol))).Select
Selection.Copy
topbot.Activate
Cells(topbot1stRow, topbotTickerCol).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

But the above is givng me a "compile error: wrong # of arguments or invalid
property assignment" at the first instance of the word 'range'.

Question: is it actually possible to accomplish what I want and, if so, how?

Thanks a lot.

selecting non-contiguous blocks of cells in vb
 

Ok, I forgot there is such a thing as union(). I have changed the code to the
following, which works to an extent, but...:

fmcv.Activate
Set fmcvData = Range(Cells(fmcvRowStart, fmcvSecidCol),
Cells(fmcvRowEnd, fmcvSecidCol))
Set fmcvData = Union(fmcvData, Range(Cells(fmcvRowStart, fmcvIssuerCol),
Cells(fmcvRowEnd, fmcvIssuerCol)))
Set fmcvData = Union(fmcvData, Range(Cells(fmcvRowStart,
fmcvPctPortfolioCol), Cells(fmcvRowEnd, fmcvPctPortfolioCol)))
fmcvData.Select
Selection.Copy
topbot.Activate
Cells(topbot1stRow, topbotTickerCol).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

....but the thing is fmcvSecidCol fmcvIssuerCol and so the result of the
copy/paste actually has the fmcvIssuerCol block of data coming first/in front
of the fmcvSecidCol block. However, I do what the pasted data to be in the
order I "selected"/"union"-ed them.

New Question: Is there a way to preserve the order?

My gut tells me know and so I need to copy/paste separately to preserve the
order.
Anyhow, just thought I might try anyway.

Thanks.


"rockhammer" wrote:

When I use the record macro from the drop down menu to select multiple
non-continguous blocks of cells to copy/paste, I get code like this:

Range("B13:B34,D13:D34,I13:I34").Select
Range("I13").Activate
Selection.Copy
Range("S13").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

I tried to duplicate the above in vb using this:

Dim fmcv As Worksheet
Dim topbot As Worksheet
' every other variable below undefined explicitly were defined as Long

fmcv.Activate
Range(Range(Cells(fmcvRowStart, fmcvSecidCol), Cells(fmcvRowEnd,
fmcvSecidCol)), _
Range(Cells(fmcvRowStart, fmcvIssuerCol), Cells(fmcvRowEnd,
fmcvIssuerCol)), _
Range(Cells(fmcvRowStart, fmcvPctPortfolioCol), Cells(fmcvRowEnd,
fmcvPctPortfolioCol))).Select
Selection.Copy
topbot.Activate
Cells(topbot1stRow, topbotTickerCol).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

But the above is givng me a "compile error: wrong # of arguments or invalid
property assignment" at the first instance of the word 'range'.

Question: is it actually possible to accomplish what I want and, if so, how?

Thanks a lot.