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

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

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

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

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

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