VBA to backfill data in each row

[alexcon]

Looking for a piece of code that can dynamically backfill data as per image above.

It should always fill back to column B and handle additional rows if new rows are added.

Any suggestions appreciated.

 

[Stiuart_W]

- Dynamically fill when? What's the trigger for the backfill?
- Backfill with what? Pictures of cats?
- Will the data to the right of the first cell filled have NO blanks?

 

[alexcon]

(Not sure if the image is loading for you, can be found here if you can't see it)

I'm going to assign it to a button macro.

Backfill with the red outlined cell.. So for item 1, it takes 0.56 as the value and fills that backwards. For Item 2 it uses 0.6042. For Item 3 it uses 0.256 (not 0.564). and so on.

The data to the right of column E can be ignored. There are NO blanks at all to the right of the first cell.

Can assume this is a formally defined "Table 1".

 

[Fluff]

Are your existing values, hard values, or the result of formula?

 

[alexcon]

Are your existing values, hard values, or the result of formula?

They're hard values

 

[Fluff]

In that case try

Sub BackFill()

   With Range("B:D").EntireColumn
      .SpecialCells(xlBlanks).Formula = "=rc[1]"
      .Value = .Value
   End With
End Sub

 

[alexcon]

In that case try

Sub BackFill()

   With Range("B:D").EntireColumn
      .SpecialCells(xlBlanks).Formula = "=rc[1]"
      .Value = .Value
   End With
End Sub

This causes it to go into an endless loop creating new blank table rows to the end of the sheet.

Is it also possible to make this with reference to "Table 1"?

 

[Fluff]

What are the header names in your table?

 

[alexcon]

What are the header names in your table?

Description, 16-Feb, 16-Mar, 16-Apr, onwards

 

[Fluff]

Try

Sub BackFill()

   With Range("Table1[[#All],[COLOR=#ff0000][RSQ3][/COLOR]:[COLOR=#0000ff][P3][/COLOR]]")
      .SpecialCells(xlBlanks).FormulaR1C1 = "=rc[1]"
      .Value = .Value
   End With
End Sub

Where the value in red needs to be the col B header & the value in blue is col D header