Construct a range in VB

S

Steve

I'm trying to create code to copy and paste a variable range. It's all
in column A, but the start cell is determined by the value of cell C2,
and the end by the value of C1.

EG: C1=200, C2=100, so I want the range created in the code below to
reflect that, ie A101:A200.

I've go the following:

Range("A" & (C2 + 1) & ":A" & (C1)).Select

This doesn't work. Please could anyone advise me how to:

1) correct the code above; and
2) then copy the resultant range and paste it to begin at cell W1.
Thanks in advance

Steve
 
N

Nick Hodge

Steve

Sub CopyAndPaste()
Range("A" & Range("C2").Value + 1 & ":A" _
& Range("C1").Value).Copy Destination:=Range("W1")
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
[email protected]
 
S

Steve

Ah, sorry, this has become awkward.

The cells in column A are for individual days, 1400 in all, from
01.01.2000. The idea was to create a range of 1 month, using the method
posted before. The 2 cells to determine the range are:

C1: =COUNTIF(A1:A1429,">"&B4)
C2: =COUNTIF(A2:A1429,">="&B6),

where the cells in column B give the first and last dates of the
selected month (01.02.00 and 29.02.00, for example).

However, using this example, what is being posted is 01.10.03-28.10.03
and 27.01.03.

Any suggestions on why?

TIA

Steve
 
Top