Set a range in a Macro to copy
 

Hi,

I need copy a cell into a range of cells in another column, where the
range is determined by the height of another column (row value of the
last non-blank character)

The following code copies the contents of cell D1 into the range
"E1","E42"). When I try to replace 42 with the value of the Match
function it does not work.

Range("D1").Select
Selection.Copy
' Match("",B:B,1) evaluates to 42 in the spread sheet, the last
non-blank row in Column B
' When used in to select the range I get a compile error.
' How do I fix this?????????
'
' Range("E1", "H" & Match("",B:B,1)).Select
'
Range("E1", "E" & 42).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Thanks,

MikeC

Set a range in a Macro to copy
 

If MATCH() will work in the worksheet, then put it in a cell (say Z100).
Then in VBA try:

Range("E1", "H" & Range("Z100").Value).Select

Have not tested this. May not work, but worth a try.

--
Gary''s Student


"MikeC" wrote:

Hi,

I need copy a cell into a range of cells in another column, where the
range is determined by the height of another column (row value of the
last non-blank character)

The following code copies the contents of cell D1 into the range
"E1","E42"). When I try to replace 42 with the value of the Match
function it does not work.

Range("D1").Select
Selection.Copy
' Match("",B:B,1) evaluates to 42 in the spread sheet, the last
non-blank row in Column B
' When used in to select the range I get a compile error.
' How do I fix this?????????
'
' Range("E1", "H" & Match("",B:B,1)).Select
'
Range("E1", "E" & 42).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Thanks,

MikeC