My macro works, but its slow, I'm trying to speed it up and there's a very
obvious way to do that.

Right now my macro fills down formulas numerous times, the problem is it
fills down to the bottom of the sheet. How can I set the range so when I
fill down it goes to the bottom of the data instead of the bottom of the
sheet (it would work great if I could get it to fill down to the first row
that doesn't have any data in it).

Right now here is an example of what I have, note how it fills down to the
bottom of the spreadsheet:

Range("A3:C3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.FillDown

Is there just a snipet of vb that I could replace the range selection line
with that would work?

Hi,

try this:

if you've got data in A1:a20, and you want to put formulas (the same
one) in b1:b20

lastrow = range("a1").end(xldown).row ' this is to find the last row
with data
range("b1").select
activecell.formula = "=a1*200" 'or whatever your formula is
activecell.copy
do
activecell.offset(1,0).select 'to select the next cell down
activesheet.paste
loop until activecell.row = lastrow

hopefully this gives you a start in the right direction.

Dave

Still Learning wrote:
My macro works, but its slow, I'm trying to speed it up and there's a very
obvious way to do that.

Right now my macro fills down formulas numerous times, the problem is it
fills down to the bottom of the sheet. How can I set the range so when I
fill down it goes to the bottom of the data instead of the bottom of the
sheet (it would work great if I could get it to fill down to the first row
that doesn't have any data in it).

Right now here is an example of what I have, note how it fills down to the
bottom of the spreadsheet:

Range("A3:C3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.FillDown

Is there just a snipet of vb that I could replace the range selection line
with that would work?

how can we tell where the bottom of the data is? what column?

--
Regards,
Tom Ogilvy

"Still Learning" wrote in message
...
My macro works, but its slow, I'm trying to speed it up and there's a very
obvious way to do that.

Right now my macro fills down formulas numerous times, the problem is it
fills down to the bottom of the sheet. How can I set the range so when I
fill down it goes to the bottom of the data instead of the bottom of the
sheet (it would work great if I could get it to fill down to the first row
that doesn't have any data in it).

Right now here is an example of what I have, note how it fills down to the
bottom of the spreadsheet:

Range("A3:C3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.FillDown

Is there just a snipet of vb that I could replace the range selection line
with that would work?

Here some code that may help you

use 1 of the 3 methods listed to get the LastRow

Dim LastRow As Long



' to get last row before a blank cell
' looking from row 1 in column a
LastRow = Range("A1").End(xlDown).Row


' to get last row before a blank cell
' looking from last row in sheet in column a
LastRow = Cells(Rows.Count, "a").End(xlUp).Row


' get last used row on sheet
LastRow = TheSheet.Cells.Find(what:="*", searchorder:=xlByRows, _
searchdirection:=xlPrevious).Row


' autofill
Range("A3:C" & LastRow).AutoFill _
Destination:=Range("A1:C18"), Type:=xlFillDefault


--
mudraker
------------------------------------------------------------------------
mudraker's Profile: http://www.excelforum.com/member.php...fo&userid=2473
View this thread: http://www.excelforum.com/showthread...hreadid=571560

Those are good suggestions from mudraker, but that doesn't answer your
question if you don't know how to use it. If you do fine. If not, then
once again I ask what column can be tested to determine the extent of the
data.

--
Regards,
Tom Ogilvy

"mudraker" wrote in
message ...

Here some code that may help you

use 1 of the 3 methods listed to get the LastRow

Dim LastRow As Long



' to get last row before a blank cell
' looking from row 1 in column a
LastRow = Range("A1").End(xlDown).Row


' to get last row before a blank cell
' looking from last row in sheet in column a
LastRow = Cells(Rows.Count, "a").End(xlUp).Row


' get last used row on sheet
LastRow = TheSheet.Cells.Find(what:="*", searchorder:=xlByRows, _
searchdirection:=xlPrevious).Row


' autofill
Range("A3:C" & LastRow).AutoFill _
Destination:=Range("A1:C18"), Type:=xlFillDefault


--
mudraker
------------------------------------------------------------------------
mudraker's Profile:
http://www.excelforum.com/member.php...fo&userid=2473
View this thread: http://www.excelforum.com/showthread...hreadid=571560

Sorry for the late reply, since I had the macro insert a column after column
"A" to be used for the formula that will be dragged down, column A will have
data down to the bottom of the sheet.


"Tom Ogilvy" wrote:

Those are good suggestions from mudraker, but that doesn't answer your
question if you don't know how to use it. If you do fine. If not, then
once again I ask what column can be tested to determine the extent of the
data.

--
Regards,
Tom Ogilvy

"mudraker" wrote in
message ...

Here some code that may help you

use 1 of the 3 methods listed to get the LastRow

Dim LastRow As Long



' to get last row before a blank cell
' looking from row 1 in column a
LastRow = Range("A1").End(xlDown).Row


' to get last row before a blank cell
' looking from last row in sheet in column a
LastRow = Cells(Rows.Count, "a").End(xlUp).Row


' get last used row on sheet
LastRow = TheSheet.Cells.Find(what:="*", searchorder:=xlByRows, _
searchdirection:=xlPrevious).Row


' autofill
Range("A3:C" & LastRow).AutoFill _
Destination:=Range("A1:C18"), Type:=xlFillDefault


--
mudraker
------------------------------------------------------------------------
mudraker's Profile:
http://www.excelforum.com/member.php...fo&userid=2473
View this thread: http://www.excelforum.com/showthread...hreadid=571560