Macro fill down question, I think its easy
 

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?

Macro fill down question, I think its easy
 

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?

Macro fill down question, I think its easy
 

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?

Macro fill down question, I think its easy
 

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

Macro fill down question, I think its easy
 

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

Macro fill down question, I think its easy
 

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