VBA range

[James]

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]

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

 

[JLatham]

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]

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]

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 before:

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]

This works perfect, thank you very much.

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 before:

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.