Copying formulae down a variable number of rows

[Ajtoo]

A sheet imports data into the first three columns of a blank sheet. This data always contains a different number of rows, i.e. it may be anything from 10 to 10,000.
I now wish to copy formulae from a separate sheet, to work on the new data in cols 1-3, to the fourth column and down the required number of rows.
How can a macro recognise how many rows to copy down?

 

[Dave Hawley]

Hi Ajtoo


Try this

Sub DoIt()
Range("A1", Range("C65536").End(xlUp)).Copy _
Destination:=Sheet2.Range("A1")
End Sub

 

[Al Chara]

The following code counts the number of used rows in column A and sets the number to the variable NumRows:
dim NumRows as long
Range("A1").Select
NumRows=Range(Selection, Selection.End(xlDown)).Count

Hope this helps.
Kind regards, Al.

 

[Anonymousity]

On 2002-03-15 07:56, Ajtoo wrote:
A sheet imports data into the first three columns of a blank sheet. This data always contains a different number of rows, i.e. it may be anything from 10 to 10,000.
I now wish to copy formulae from a separate sheet, to work on the new data in cols 1-3, to the fourth column and down the required number of rows.
How can a macro recognise how many rows to copy down?

Dim NumRows As Long
NumRows = Intersect(ActiveSheet.UsedRange, Columns("A:C")).Rows.Count