Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA range
I have this line of code and it works but it is dependent on having 100 rows.
I would like to change it up so that it will not be dependednt on having a certain amount of rows. Sheets("Raw Data").Range("A7:A100").Copy Destination:=Range("A2") It is just copying data from the worksheet "Raw Data" to another sheet in the same workbook. I just never know how many samples I have and would like to change the code to be a little more flexiable. I thought the change would of looked like this: Sheets("Raw Data").Range("A7:A").Copy Destination:=Range("A2") This didn't work though. Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA range
How about starting from A7 to the last used cell in column A.
If that's ok, I like this... Dim RngToCopy as range dim DestCell as range with worksheets("raw data") set rngtocopy = .range("a7",.cells(.rows.count,"A").end(xlup)) end with set destcell = activesheet.range("a2") rngtcopy.copy _ destination:=destcell James wrote: I have this line of code and it works but it is dependent on having 100 rows. I would like to change it up so that it will not be dependednt on having a certain amount of rows. Sheets("Raw Data").Range("A7:A100").Copy Destination:=Range("A2") It is just copying data from the worksheet "Raw Data" to another sheet in the same workbook. I just never know how many samples I have and would like to change the code to be a little more flexiable. I thought the change would of looked like this: Sheets("Raw Data").Range("A7:A").Copy Destination:=Range("A2") This didn't work though. Thanks -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA range
Change the code to look something like this. I added the additional testing
to make sure you never accidentally grab rows ABOVE row 7 if things happen to be empty for some reason: Dim lastRow As Long lastRow = Sheets("Raw Data").Range("A" & _ Rows.Count).End(xlUp).Row If lastRow < 7 Then lastRow = 7 End If Sheets("Raw Data").Range("A7:A" & _ lastRow).Copy Destination:=Range("A2") variable lastRow will be set to the largest numbered row on the Raw Data sheet that has either a formula or value in it; with 7 being the smallest row number it will pick up with the check we put into the code. "James" wrote: I have this line of code and it works but it is dependent on having 100 rows. I would like to change it up so that it will not be dependednt on having a certain amount of rows. Sheets("Raw Data").Range("A7:A100").Copy Destination:=Range("A2") It is just copying data from the worksheet "Raw Data" to another sheet in the same workbook. I just never know how many samples I have and would like to change the code to be a little more flexiable. I thought the change would of looked like this: Sheets("Raw Data").Range("A7:A").Copy Destination:=Range("A2") This didn't work though. Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA range
and I thought it would be something a little easier. Do you think you can
give me another hand with it: I want to take all the data in cells A7 to G7 in the "Raw Data" worksheet and place them in my "Renormalized wt%" worksheet starting in cell A2 through G2. Again I never know how much data will come it varies from 10 samples to 1000+ samples. I appreciate the help, thank you for the assistance thus far. "JLatham" wrote: Change the code to look something like this. I added the additional testing to make sure you never accidentally grab rows ABOVE row 7 if things happen to be empty for some reason: Dim lastRow As Long lastRow = Sheets("Raw Data").Range("A" & _ Rows.Count).End(xlUp).Row If lastRow < 7 Then lastRow = 7 End If Sheets("Raw Data").Range("A7:A" & _ lastRow).Copy Destination:=Range("A2") variable lastRow will be set to the largest numbered row on the Raw Data sheet that has either a formula or value in it; with 7 being the smallest row number it will pick up with the check we put into the code. "James" wrote: I have this line of code and it works but it is dependent on having 100 rows. I would like to change it up so that it will not be dependednt on having a certain amount of rows. Sheets("Raw Data").Range("A7:A100").Copy Destination:=Range("A2") It is just copying data from the worksheet "Raw Data" to another sheet in the same workbook. I just never know how many samples I have and would like to change the code to be a little more flexiable. I thought the change would of looked like this: Sheets("Raw Data").Range("A7:A").Copy Destination:=Range("A2") This didn't work though. Thanks |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA range
Actually, it sounds like you want to take all the data in cells A7 to G#
where # is the last row used? Hope I got that right, because that's the solution you are about to get. The "last row used" will be based on the entries in column A, just as befo Dim lastRow As Long lastRow = Sheets("Raw Data").Range("A" & _ Rows.Count).End(xlUp).Row If lastRow < 7 Then lastRow = 7 End If Sheets("Raw Data").Range("A7:G" & _ lastRow).Copy Destination:=Range("A2") How's that? Only change is in the last instruction where I changed Range("A7:A" to Range("A7:G" within the instruction. "James" wrote: and I thought it would be something a little easier. Do you think you can give me another hand with it: I want to take all the data in cells A7 to G7 in the "Raw Data" worksheet and place them in my "Renormalized wt%" worksheet starting in cell A2 through G2. Again I never know how much data will come it varies from 10 samples to 1000+ samples. I appreciate the help, thank you for the assistance thus far. "JLatham" wrote: Change the code to look something like this. I added the additional testing to make sure you never accidentally grab rows ABOVE row 7 if things happen to be empty for some reason: Dim lastRow As Long lastRow = Sheets("Raw Data").Range("A" & _ Rows.Count).End(xlUp).Row If lastRow < 7 Then lastRow = 7 End If Sheets("Raw Data").Range("A7:A" & _ lastRow).Copy Destination:=Range("A2") variable lastRow will be set to the largest numbered row on the Raw Data sheet that has either a formula or value in it; with 7 being the smallest row number it will pick up with the check we put into the code. "James" wrote: I have this line of code and it works but it is dependent on having 100 rows. I would like to change it up so that it will not be dependednt on having a certain amount of rows. Sheets("Raw Data").Range("A7:A100").Copy Destination:=Range("A2") It is just copying data from the worksheet "Raw Data" to another sheet in the same workbook. I just never know how many samples I have and would like to change the code to be a little more flexiable. I thought the change would of looked like this: Sheets("Raw Data").Range("A7:A").Copy Destination:=Range("A2") This didn't work though. Thanks |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA range
This works perfect, thank you very much.
"JLatham" wrote: Actually, it sounds like you want to take all the data in cells A7 to G# where # is the last row used? Hope I got that right, because that's the solution you are about to get. The "last row used" will be based on the entries in column A, just as befo Dim lastRow As Long lastRow = Sheets("Raw Data").Range("A" & _ Rows.Count).End(xlUp).Row If lastRow < 7 Then lastRow = 7 End If Sheets("Raw Data").Range("A7:G" & _ lastRow).Copy Destination:=Range("A2") How's that? Only change is in the last instruction where I changed Range("A7:A" to Range("A7:G" within the instruction. "James" wrote: and I thought it would be something a little easier. Do you think you can give me another hand with it: I want to take all the data in cells A7 to G7 in the "Raw Data" worksheet and place them in my "Renormalized wt%" worksheet starting in cell A2 through G2. Again I never know how much data will come it varies from 10 samples to 1000+ samples. I appreciate the help, thank you for the assistance thus far. "JLatham" wrote: Change the code to look something like this. I added the additional testing to make sure you never accidentally grab rows ABOVE row 7 if things happen to be empty for some reason: Dim lastRow As Long lastRow = Sheets("Raw Data").Range("A" & _ Rows.Count).End(xlUp).Row If lastRow < 7 Then lastRow = 7 End If Sheets("Raw Data").Range("A7:A" & _ lastRow).Copy Destination:=Range("A2") variable lastRow will be set to the largest numbered row on the Raw Data sheet that has either a formula or value in it; with 7 being the smallest row number it will pick up with the check we put into the code. "James" wrote: I have this line of code and it works but it is dependent on having 100 rows. I would like to change it up so that it will not be dependednt on having a certain amount of rows. Sheets("Raw Data").Range("A7:A100").Copy Destination:=Range("A2") It is just copying data from the worksheet "Raw Data" to another sheet in the same workbook. I just never know how many samples I have and would like to change the code to be a little more flexiable. I thought the change would of looked like this: Sheets("Raw Data").Range("A7:A").Copy Destination:=Range("A2") This didn't work though. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I enter formula sum(range+range)*0.15 sumif(range=3) | Excel Discussion (Misc queries) | |||
Excel Addin:Setting the range to the Excel.Range object range prop | Excel Worksheet Functions | |||
Selecting range in list of range names depending on a cell informa | Excel Discussion (Misc queries) | |||
formula to sort a range so that it matches the exact rows of a column that is outside that range? | Excel Discussion (Misc queries) | |||
How to count dates within a certain range in a column with mutiple date range entries | Excel Worksheet Functions |