Home > Software engineering >  Autofill Table Column VBA
Autofill Table Column VBA

Time:12-04

I am looking to autofill the first column (labeled "ID") with sequential numbers starting from 1,2,3,... so on. So far what I have is

    Application.DisplayAlerts = True

Dim TradeTable As Excel.ListObject
Set TradeTable = Sheets("Pre Trade").ListObjects("PreTradeTable")

TradeTable.DataBodyRange(1, 1).Select
Selection.Value = 1

TradeTable.DataBodyRange(2, 1).Select
Selection.Value = 2

TradeTable.ListColumns("ID").DataBodyRange.Select
Selection.AutoFill

It does not work. Plus I do not like the non-dynamic reference e.g. (2,1). Is there a way to specify first item in Column Header "ID" and then 2nd cell and then do the autofill?

CodePudding user response:

Set TradeTable = Sheets("Pre Trade").ListObjects("PreTradeTable")
With TradeTable.ListColumns("ID").DataBodyRange
    .Cells(1) = 1
    .DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1
End With

CodePudding user response:

I don't know how to help you with that particular approach but I can suggest another approach.

Set IdCells = TradeTable.ListColumns("ID").DataBodyRange

will give you a range of the cells you need to fill. Then,

For i = 1 to IdCells.count
  IdCells(i).value = i
Next i

will populate them, if I remember my syntax correctly.

  • Related