Quick Macro helpl

[cav~firez22]

If i have this

    ActiveCell.FormulaR1C1 = "ATLANTA"
    Selection.AutoFill Destination:=Range("B1:B150")

How can i get it to auto fill down to the last row of data instead of always row 150.

basically as far as there is data in colum A, i want B1 to autofill down.

could some one help me get that?

thanx

 

[Smitty]

How's this:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> foo()
    <SPAN style="color:#00007F">Dim</SPAN> lastrow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
        lastrow = Cells(Rows.Count, "A").End(xlUp).Row
        <SPAN style="color:#00007F">With</SPAN> Range("B1")
            .Value = "ATLANTA"
            .AutoFill Destination:=Range(Range("B1"), Cells(lastrow, ActiveCell.Column))
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

Smitty

 

[Boller]

Replace both lines of code with :-

Range([B1], [A65536].End(xlUp)(1, 2)) = "ATLANTA"

 

[cav~firez22]

Cool thanks..

How would i select A:G to the lastrow? and copy it to another workbook?

 

[Smitty]

You could use the same general idea:

Range([A1], Cells(Rows.Count, "G").end(xlUp)).Copy

Just record copying something, opening the other wb and pasting to get the syntax for the rest.

Smitty

 

[cav~firez22]

Excellent.. Thank you