Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Sep 2003
    Location
    Camp Springs, Kentucky, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Autofill in VBA (Excel 2002/SP2)

    I'm reading a tab delimited ASCII file into Excel and the number of rows will change with each file that I read in. I need to add several new columns of formulas for this data. Is there an easy way to determine the number of rows that have data so that I can fill (or autofill) the formula in the new columns. When I record a macro, and perform an autofill, it records the absolute cell number for the end.

    Thanks,
    Joe

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Autofill in VBA (Excel 2002/SP2)

    You can get the last row containing something in a particular column by using something like(eg in Col [img]/forums/images/smilies/cool.gif[/img]:

    <pre>Dim lLastRow
    lLastRow = Range("B65536").End(xlUp).Row</pre>


    You can get the entire range:
    <pre>Dim sAddress as string
    sAddress = Range(Range("B1"),Range("B65536").End(xlUp)).addre ss</pre>


    Does this help, or do you have a more specific question in mind?

    Steve

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Autofill in VBA (Excel 2002/SP2)

    Here try this routine for autofill

    <pre>Option Explicit
    Sub Autofill(sStartingCell As String, iCol As Integer)
    Dim lLastRow As Long
    Dim rng As Range

    Set rng = Range(sStartingCell)
    lLastRow = Cells(65536, iCol).End(xlUp).Row
    rng.Autofill _
    Destination:=Range(rng, Cells(lLastRow, rng.Column))
    Set rng = Nothing
    End Sub
    </pre>


    You can call it in VB using something like:
    <pre> Call Autofill("C1", 1)</pre>


    The first cell is strting address, and the second is the column number to compare
    This will Autofill from C1 to the last filled row in A

    Or even something like:
    <pre> Call Autofill("C1:C2", 1)</pre>


    If you have the start of a series


    Steve

  4. #4
    Lounger
    Join Date
    Sep 2003
    Location
    Camp Springs, Kentucky, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autofill in VBA (Excel 2002/SP2)

    Steve,

    You're amazing. This worked perfectly.

    Thanks!

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Autofill in VBA (Excel 2002/SP2)

    Glad I could help

    Steve

  6. #6
    Star Lounger
    Join Date
    Jun 2002
    Location
    Seymour, USA
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autofill in VBA (Excel 2002/SP2)

    Could I also do something like Call Autofill ("C1", 1, 3, 4, 6) ?

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Autofill in VBA (Excel 2002/SP2)

    What are the other parameters for?

    The macro as written only needs 2 parameters: the cell to copy and the "full column" to to determine the length of the autofill.

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •