locked
Fill Down with Macro RRS feed

  • Question

  • I'm trying to write a macro that automatically fills down from cell C4 and fills to the amount of existing rows in a data set. I enter a formula into C4 and F4, and after formatting the data set (and right before I make it a table), I want to fill down those formulas. What I'm finding is that it fills down to a specific cell when creating the macro, and then always fills to that cell. Unfortunately, my data set changes the number of records each time, so it's either filling too far, or not far enough. I know it's user error, but I don't know how to write it so that it'll fill down. Help!!

    Thank you!

    My macro so far is as follows (obviously, I've deactivated the lines of code that I've tried and failed, but they're in there): 

      Columns("H:H").Select
        Application.CutCopyMode = False
        Selection.Delete Shift:=xlToLeft
        Selection.Delete Shift:=xlToLeft
        Selection.Delete Shift:=xlToLeft
        Selection.Delete Shift:=xlToLeft
        Selection.Delete Shift:=xlToLeft
        Selection.Delete Shift:=xlToLeft
        Selection.Delete Shift:=xlToLeft
        Selection.Delete Shift:=xlToLeft
        Selection.Delete Shift:=xlToLeft
        Columns("I:M").Select
        Selection.Delete Shift:=xlToLeft
        Columns("J:O").Select
        Selection.Delete Shift:=xlToLeft
        Range("B1:G1").Select
        Selection.Cut Destination:=Range("L1:Q1")
        Columns("B:B").Select
        Selection.Delete Shift:=xlToLeft
        Selection.Delete Shift:=xlToLeft
        Columns("E:E").Select
        Selection.Delete Shift:=xlToLeft
        Selection.Insert Shift:=xlToRight
        Columns("C:C").Select
        Selection.Insert Shift:=xlToRight
        Range("C3").Select
        ActiveCell.FormulaR1C1 = "sku2"
        Range("F3").Select
        ActiveCell.FormulaR1C1 = "qty calc"
        Range("C4").Select
        ActiveCell.FormulaR1C1 = "=MID(RC[-1],FIND(""-"",RC[-1])+1,LEN(RC[-1]))"
        Range("K1:P1").Select
        Selection.Cut Destination:=Range("B1:G1")
        Range("F4").Select
        ActiveCell.FormulaR1C1 = "=IFERROR(IF(RC[-1]<R1C3,0,R1C4),0)"
        Range("C4").Select
        'Selection.End(x1Down).Select
        'ActiveCell.Offset(0, 1).Select
        'Range(Selection, Selection.End(x1Up)).Select
        'Selection.FillDown
        'ActiveCell.Range("A1:A3560").Select
        Range("A3").Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlDown)).Select
         Dim tbl As ListObject
        Set tbl = ActiveSheet.ListObjects.Add(xlSrcRange, Selection, , xlYes)
        tbl.TableStyle = "TableStyleMedium15"
        Range("A1").Select
    End Sub

    Sunday, February 25, 2018 9:20 PM

All replies

  • Hello Judi_D,

    You need get last row index of the table. I note that column A keep unchanged while formatting the sheet. I would suggest you get last row of column A and use it as the last row of the table.

    Here is the example code.

    Sub Test()
     Columns("H:H").Select
        Application.CutCopyMode = False
        Selection.Delete Shift:=xlToLeft
        Selection.Delete Shift:=xlToLeft
        Selection.Delete Shift:=xlToLeft
        Selection.Delete Shift:=xlToLeft
        Selection.Delete Shift:=xlToLeft
        Selection.Delete Shift:=xlToLeft
        Selection.Delete Shift:=xlToLeft
        Selection.Delete Shift:=xlToLeft
        Selection.Delete Shift:=xlToLeft
        Columns("I:M").Select
        Selection.Delete Shift:=xlToLeft
        Columns("J:O").Select
        Selection.Delete Shift:=xlToLeft
        Range("B1:G1").Select
        Selection.Cut Destination:=Range("L1:Q1")
        Columns("B:B").Select
        Selection.Delete Shift:=xlToLeft
        Selection.Delete Shift:=xlToLeft
        Columns("E:E").Select
        Selection.Delete Shift:=xlToLeft
        Selection.Insert Shift:=xlToRight
        Columns("C:C").Select
        Selection.Insert Shift:=xlToRight
        Range("C3").Select
        ActiveCell.FormulaR1C1 = "sku2"
        Range("F3").Select
        ActiveCell.FormulaR1C1 = "qty calc"
        Range("C4").Select
        ActiveCell.FormulaR1C1 = "=MID(RC[-1],FIND(""-"",RC[-1])+1,LEN(RC[-1]))"
        Range("K1:P1").Select
        Selection.Cut Destination:=Range("B1:G1")
        Range("F4").Select
        ActiveCell.FormulaR1C1 = "=IFERROR(IF(RC[-1]<R1C3,0,R1C4),0)"
        Range("A3").Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlDown)).Select
        Dim tbl As ListObject
        Set tbl = ActiveSheet.ListObjects.Add(xlSrcRange, Selection, , xlYes)
        tbl.TableStyle = "TableStyleMedium15"
        
        
        lastROW = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
        Range("C4").Select
        Selection.AutoFill Destination:=Range("C4:C" & lastROW)
        Range("F4").Select
        Selection.AutoFill Destination:=Range("F4:F" & lastROW)
    End Sub

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, February 26, 2018 1:45 AM