Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Auto Filldown (Xls 2003)

    Hi all,

    I am not sure why the following codes doesn't work if there is a header in col A. How do I modify it to include a header?


    Sub FillDown()

    Dim iLastRow As Long
    Dim iNext As Long
    Dim i As Long

    With ActiveSheet

    i = 1
    iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    Do Until i >= iLastRow
    iNext = .Cells(i, "A").End(xlDown).Offset(-1).Row
    .Cells(i, "A").AutoFill .Cells(i, "A").Resize(iNext - i + 1) <======== Run Time error "1004" AutoFill method of range class failed
    i = iNext + 1
    Loop
    End With
    End Sub

    thanks, fy
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  2. #2
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Filldown (Xls 2003)

    Hi Jerry,

    the codes doesn't filldown blank cells with value above it as expected, its only replaced the cell (A2) with the contents of the header

    thanks, fy
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Auto Filldown (Xls 2003)

    <P ID="edit" class=small>(Edited by Jezza on 23-Dec-07 17:32. Now leaves header untouched)</P>Hi Fy

    Try this:

    Sub FillDown()

    Dim iLastRow As Long
    Dim iNext As Long
    Dim i As Long

    With ActiveSheet

    i = 1
    iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    iNext = .Cells(i + 1, "A").End(xlDown).Offset(-1).Row
    .Cells(i + 1, "A").AutoFill .Cells(i + 1, "A").Resize(iNext - i + 1)

    End With
    End Sub
    Jerry

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Auto Filldown (Xls 2003)

    Eeek I have changed it whilst you were replying.

    I have now put in a word in A2 and it now copies down to A65536, is this what you are looking for?
    Jerry

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Auto Filldown (Xls 2003)

    If I analysed your code correctly, it seems to autofill the A column with the value in A1. If this is the case, all you need is this one liner...(and its not a joke <img src=/S/grin.gif border=0 alt=grin width=15 height=15>)

    Sub FillDown()
    Range("A1").CurrentRegion.Columns(1).Value = Range("A1").Value
    End Sub

    Or if you want to retain a column heading:

    Sub FillDown()
    Range("A1").CurrentRegion.Offset(1, 0).Resize(Range("A1").CurrentRegion.Rows.Count - 1).Columns(1).Value = Range("A2").Value
    End Sub
    Regards,
    Rudi

  6. #6
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Auto Filldown (Xls 2003)

    I thought this may work as well Rudi

    Sub FillDown()
    Dim iLastRow As Long

    iLastRow = Cells(Rows.Count, "A").End(xlUp).Row

    Range("A" & iLastRow & ":A" & iLastRow + 1).FillDown
    End Sub
    Jerry

  7. #7
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Filldown (Xls 2003)

    Hi Jerry

    I think the codes that I have posted earlier doesn't clearly stated what I want to accomplish. I am trying to fill down the values to the blank cells with
    the value above. The codes in my post does do this if the the starting row to filldown is A1, however, it give an error msg if I add in a header.

    thanks, fy
    Attached Images Attached Images
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  8. #8
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Auto Filldown (Xls 2003)

    Hi Fy

    Sorry, this will work for you:

    <pre>Sub FillDown()

    Dim wks As Worksheet
    Dim rng As Range
    Dim LastRow As Long
    Dim col As Long

    Set wks = ActiveSheet
    With wks
    col = .Range("a1").Column

    Set rng = .UsedRange
    LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
    Set rng = Nothing
    On Error Resume Next
    Set rng = .Range(.Cells(2, col), .Cells(LastRow, col)) _
    .Cells.SpecialCells(xlCellTypeBlanks)
    On Error GoTo 0

    If rng Is Nothing Then
    MsgBox "No blanks found in current column"
    Exit Sub
    Else
    rng.FormulaR1C1 = "=R[-1]C"
    End If


    With .Cells(1, col).EntireColumn
    .Value = .Value
    End With

    End With

    End Sub
    </pre>

    Jerry

  9. #9
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Auto Filldown (Xls 2003)

    Ah, thats a clearer explanation...
    Here is another version! (I like competing with Jerry! <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

    Sub FillCells()
    Dim FillRng As Range
    On Error GoTo EH
    Set FillRng = Range("A1", Range("A65536").End(xlUp)).SpecialCells(xlCellType Blanks)
    FillRng.FormulaR1C1 = "=R[-1]C"
    Exit Sub
    EH:
    MsgBox Err.Description, vbExclamation
    End Sub
    Regards,
    Rudi

  10. #10
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Auto Filldown (Xls 2003)

    Hi FY
    For both Jerry and Rudi's solution, if you wish to fill in cells below A6 you will need a "Stop Cell" similar to A11 in the attachment.
    Attached Images Attached Images
    Regards
    Don

  11. #11
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Filldown (Xls 2003)

    Hi Rudi,Jerry

    thank for the codes. how do I modify the codes to include other columns beside Col A, and also if let say, I want to fill 5 rows down
    after the last non blank row.
    Would appreciate if you can explain a little in FormulaR1C1 which confuse me. I have looked up in Help but not much help from there.

    Do you know why doesn't my codes works.

    thanks, fy
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto Filldown (Xls 2003)

    Your macro works for me, so I suspect that you have (as usual) not given us complete information.

    If you want to fill more columns, you can modify the code like this:

    Sub FillDown()
    Const sFirstCol = "A"
    Const sLastCol = "D"
    Dim iLastRow As Long
    Dim iNext As Long
    Dim i As Long
    i = 1
    iLastRow = Cells(Rows.Count, sFirstCol).End(xlUp).Row
    Do Until i >= iLastRow
    iNext = Cells(i, sFirstCol).End(xlDown).Offset(-1).Row
    Range(sFirstCol & i & ":" & sLastCol & i).AutoFill _
    Range(sFirstCol & i & ":" & sLastCol & iNext)
    i = iNext + 1
    Loop
    End Sub

    Change the constants sFirstCol and sLastCol at the beginning of the macro as needed to indicate the first and last columns you want to fill.
    If you want to fill down below the last header, simply enter something in the row below the last one you want to fill.

    About FormulaR1C1: Excel has two ways to refer to cells: A1 and R1C1. You can select the way you want to use in the General tab of Tools | Options...
    See About cell and range references for more info.
    In VBA, you can create formulas in A1 notation by setting the Formula property, or formulas in R1C1 notation by setting the FormulaR1C1 property, regardless of the setting in Tools | Options...

  13. #13
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Filldown (Xls 2003)

    Hi Hans,

    Thank for looking into this and for the link, I will read it up and hopefully I can gain more insights on this.

    I encounter the same error msg as my first post ==== Run-time error "1004": Autofill method of Range calss failed

    at this line : Range(sFirstCol & i & ":" & sLastCol & i).AutoFill _
    Range(sFirstCol & i & ":" & sLastCol & iNext)

    thanks, fy
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto Filldown (Xls 2003)

    Could you attach a small sample workbook with some dummy data and the macro in which this error occurs?

  15. #15
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Filldown (Xls 2003)

    Hi Hans,

    I have attached the sample

    thanks, fy
    Attached Files Attached Files
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

Page 1 of 2 12 LastLast

Posting Permissions

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