Autofill across based on previous row

[krbreddy]

Hi,

I am trying to autofill vlookup across row, till the last column.

My excel have few sheets. Each sheet, number of columns is not fixed.
First row has codes. I am getting descriptions for these codes from another sheet into current sheet.

I insert a row below to first row, then trying to autofill across the row, based on the previous row(above cell). If above cell is empty, dont apply formula.

[TABLE="width: 576"]
<colgroup><col width="64" span="9" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64"]D1[/TD]
[TD="class: xl63, width: 64"]D2[/TD]
[TD="class: xl63, width: 64"]D3[/TD]
[TD="class: xl63, width: 64"]D4[/TD]
[TD="class: xl63, width: 64"]D5[/TD]
[TD="class: xl63, width: 64"]D6[/TD]
[TD="class: xl63, width: 64"]D7[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl63"]Monday[/TD]
[TD="class: xl63"]Tuesday[/TD]
[TD="class: xl63"]Wednesday[/TD]
[TD="class: xl63"]Thursday[/TD]
[TD="class: xl63"]Friday[/TD]
[TD="class: xl63"]Saturday[/TD]
[TD="class: xl63"]Sunday[/TD]
[TD="class: xl63"]Monday[/TD]
[TD="class: xl63"]Tuesday[/TD]
[/TR]
</tbody>[/TABLE]

The code I write looks like this

Range("A1").Select
ActiveCell.Offset(1).EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrAbove



Set rng = ActiveSheet.Range("A2")


FinalResult = Application.WorksheetFunction.VLookup(Range("A1"), Sheet1.Range("A:B"), 2, False)
rng = FinalResult


Range("A2").Select


Selection.AutoFill Destination:=Range("A2:I2")

I want it to be dynamic based on previous row cell or column count .. instead of A2:I2

Can some one help me how to fix it and make general ?

Thanks in advance.