Solved: Copy unknown range size

[Maurice Buck]

I have a variable called NoOfPlayers

I want to select a range starting at B1
The range will be 3 columns wide i.e. B1 to D1
The number of rows copied to be the aforementioned variable NoOfPlayers

Thanks in advance

[ntrauger]

Try something along the lines of:Range("B1:D" & NoOfPlayers).Copy '...

[CharlesH]

HI,

Try this.
Change the sheet name to that of your sheet name.



Sub test()
dim NoOfPlayers as Long
Sheets("Sheet1").Range("B1:D" & Sheets("Sheet1").range("A65536").End(XlUp).copy
End Sub

[Maurice Buck]

Thanks for the help
New problem!
When the variable NoOfPlayerTb1 is 9 only 8 rows are copied, why?

Sheets("ReDrawSheet").Range("B2:D" & NoOfPlayersTb1).Copy

[Kenneth Hobs]

Since the number of rows in B2:D9 is 8, then that is as expected.

You might want to do this:

Sheets("ReDrawSheet").Range("B2:D" & (NoOfPlayersTb1+1)).Copy
Generally, one would use a method like Charles posted. When you go up like that, be sure to go up to the row in the column where the last data is at. That may not be column A.

Using the example that Charles posted, you might want something like:
Sheets("Sheet1").Range("B1:D" & Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Row).Copy

[mikerickson]

In this case, you might want to use the .Resize property.

Sheets("ReDrawSheet").Range("B2").Resize(NoofPlayers, 4).Copy

[Maurice Buck]

Thanks everyone, my problem is now solved

[Maurice Buck]

Thanks again everyone for your help