View Full Version : Solved: Copy unknown range size
Maurice Buck
06-22-2011, 03:25 PM
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
06-22-2011, 03:50 PM
Try something along the lines of:Range("B1:D" & NoOfPlayers).Copy '...
CharlesH
06-22-2011, 04:18 PM
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
06-22-2011, 04:47 PM
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
06-22-2011, 05:16 PM
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
06-22-2011, 05:24 PM
In this case, you might want to use the .Resize property.
Sheets("ReDrawSheet").Range("B2").Resize(NoofPlayers, 4).Copy
Maurice Buck
06-23-2011, 01:23 AM
Thanks everyone, my problem is now solved
Maurice Buck
06-23-2011, 01:27 AM
Thanks again everyone for your help
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.