Hello Judi_D，

您需要获取表格的最后一行索引。我注意到格式化工作表时A列保持不变。我建议你获得A列的最后一行，并将其用作表格的最后一行。

这是示例代码。

子测试（）
列（&H; H"）。选择
 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"）。选择
 Selection.Delete Shift：= xlToLeft 
列（"J：O"）。选择
 Selection.Delete Shift：= xlToLeft 
范围（" B1：G1"）。选择
 Selection.Cut目的地：=范围（"L1：Q1"）
列（" B：B"）。选择
 Selection.Delete Shift：= xlToLeft 
 Selection.Dele te Shift：= xlToLeft 
列（"E：E"）。选择
 Selection.Delete Shift：= xlToLeft 
 Selection.Insert Shift：= xlToRight 
列（" C：C"）。选择
 Selection.Insert Shift：= xlToRight 
范围（"C3"）。选择
 ActiveCell.FormulaR1C1 =" sku2" 
范围（"F3"）。选择
 ActiveCell.FormulaR1C1 =" qty calc" 
范围（" C4"）。选择
 ActiveCell.FormulaR1C1 =" = MID（RC [-1]，FIND（""  - ""，RC [-1]） + 1，LEN（RC [-1]））" 
范围（"K1：P1"）。选择
 Selection.Cut目的地：=范围（< B1：G1"）
范围（" F4"）。选择
 ActiveCell.FormulaR1C1 ="= IFERROR（IF（RC [-1]< R1C3,0，R1C4），0）" 
范围（" A3"）。选择
范围（选择，选择。结束（xlToRight））。选择
范围（选择，选择。结束（xlDown））。选择
 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"）。选择
 Selection.AutoFill目的地：=范围（"C4：C"& lastROW）
范围（"F4"）。选择
 Selection.AutoFill目的地：=范围（"F4：F"& ; lastROW）
 End Sub 

***的问候，

Terry

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

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