PDA

View Full Version : Copy to Last Row



atlas215
02-14-2010, 03:21 PM
Hello All -

I am a first time poster on this forum though i was looking for a little help to what i believe is a simple issue.

I have an excel sheet where the user will load raw data onto the first tab of the sheet - on the second tab I am looking to code formulas and such to perform analysis on the data. What I am looking for help on is an easy way to define and reference the last row of data on the first tab. This sheet will be used daily or so with different sets of data and so the last row will not be defined.

For instance -on day one i have 2000 rows of data - i would like my sheet to perform all of the analysis in the second tab and copy them down and such for all of those 2000 lines but no more and similarly on day two if the data is not 5000 lines i would want the referential analysis to be done for each of the 5000 lines.

I know i could do this with a loop until statement though i am looking for a more efficient way to define this range.

Thanks -- and please let me know if this is unclear.

AA

lucas
02-14-2010, 03:49 PM
This will find the last row on sheet 1.

Sub a()
Dim rng As Range
Set rng = Sheets("Sheet1").UsedRange
MsgBox rng(rng.Count).Row
End Sub

atlas215
02-14-2010, 03:52 PM
How then would I reference that? For instance, if i wanted to copy a formula down to the last active row?

lucas
02-14-2010, 03:55 PM
Is there always data in one particular column? A for instance?

lucas
02-14-2010, 03:59 PM
Are you going to put the formula's on sheet 2 or sheet 1?

Questions.....

atlas215
02-14-2010, 04:01 PM
Column A will always have data (without blanks) on sheet 1 - though the analysis will be done on sheet 2, 3, and so one

lucas
02-14-2010, 04:34 PM
Maybe this will get you started:

see attached.

atlas215
02-14-2010, 05:28 PM
Thanks - i will have to alter the syntax a little for mine though i think i understand .