VBA code for autofill until empty

[DRG1978]

Hi All,

Can you please help me by adjusting this VBA for autofill until empty and not until cell "C346"?

Thank you very much!!


' Keyboard Shortcut: Ctrl+y
'
Columns("A:A").Select
Selection.ColumnWidth = 8.57
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Columns("C:G").Select
Selection.Delete Shift:=xlToLeft
Columns("H:AK").Select
Selection.Delete Shift:=xlToLeft
Columns("F:F").Select
Selection.Delete Shift:=xlToLeft
Columns("C:C").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("C1").Select
ActiveCell.FormulaR1C1 = "PM"
Range("D1").Select
ActiveCell.FormulaR1C1 = "PT"
Range("C2").Select
Sheets("INV View").Select
Sheets("Data").Visible = True
Sheets("INV View").Select
Range("C2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],Data!C[-2]:C[-1],2,FALSE)"
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C346")
Range("C2:C346").Select
Range("D2").Select
End Sub

 

[PatOBrien198]

Try:

Selection.AutoFill Destination:=Range(Selection.Address & ":" & Selection.End(xlDown).Offset(-1, 0).Address)

 

[DRG1978]

Thank you!

I still see #N/A.

This is how i change it. Could it be because of the rage in bold?

Columns("A:A").Select
Selection.ColumnWidth = 8.57
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Columns("C:G").Select
Selection.Delete Shift:=xlToLeft
Columns("H:AK").Select
Selection.Delete Shift:=xlToLeft
Columns("F:F").Select
Selection.Delete Shift:=xlToLeft
Columns("C:C").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("C1").Select
ActiveCell.FormulaR1C1 = "PM"
Range("D1").Select
ActiveCell.FormulaR1C1 = "PT"
Range("C2").Select
Sheets("INV View").Select
Sheets("Data").Visible = True
Sheets("INV View").Select
Range("C2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],Data!C[-2]:C[-1],2,FALSE)"
Range("C2").Select
Selection.AutoFill Destination:=Range(Selection.Address & ":" & Selection.End(xlDown).Offset(-1, 0).Address)
Range("C2:C346").Select
Range("D2").Select
End Sub

 

[Joe4]

Try replacing all this:

[COLOR=#333333]Range("C2").Select[/COLOR]
[COLOR=#333333]ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],Data!C[-2]:C[-1],2,FALSE)"[/COLOR]
[COLOR=#333333]Range("C2").Select[/COLOR]
[COLOR=#333333]Selection.AutoFill Destination:=Range(Selection.Address & ":" & Selection.End(xlDown).Offset(-1, 0).Address)[/COLOR]
[B]Range("C2:C346").Select[/B]

with just this:

[COLOR=#333333]Range("C2:C" & Cells(Rows.Count,"B").End(xlUp).Row).[/COLOR][COLOR=#333333]FormulaR1C1 = "=VLOOKUP(RC[-2],Data!C[-2]:C[-1],2,FALSE)"[/COLOR]

 

[DRG1978]

You are the MAN!!!
Thank you very much, Joe4!!
Much appreciated!!
Take care!!

 

[Joe4]

You are welcome.

 

[DRG1978]

Hi Joe4,

Hope all is ok with you!

Joe, i have another VBA for autofill.

Can you please assist me?

Thank you very much!!!

Sub Item_Stock()
'
' Item_Stock Macro
'
' Keyboard Shortcut: Ctrl+y
'
Application.CutCopyMode = False
With Selection
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Rows("1:2").Select
Selection.Delete Shift:=xlUp
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B1").Select
Selection.Copy
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Date"
Columns("B:N").Select
Selection.AutoFilter
ActiveSheet.Range("$B$1:$N$115326").AutoFilter Field:=3, Criteria1:="=NO", _
Operator:=xlOr, Criteria2:="="
ActiveSheet.Range("$B$1:$N$115326").AutoFilter Field:=5, Criteria1:="AMS"
Range("B1").Select
Selection.AutoFilter
Range("A2").Select
ActiveCell.FormulaR1C1 = "5/23/2018"
Range("A2").Select
Selection.NumberFormat = "[$-en-US]d-mmm-yy;@"
Selection.AutoFill Destination:=Range("A2:A115324")
Range("A2:A115324").Select
Selection.AutoFill Destination:=Range("A2:A115324"), Type:=xlFillCopy
Range("A2:A115324").Select
Columns("A:N").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$N$115326").AutoFilter Field:=4, Criteria1:="=NO", _
Operator:=xlOr, Criteria2:="="
ActiveSheet.Range("$A$1:$N$115326").AutoFilter Field:=6, Criteria1:="AMS"
Range("B1").Select
ActiveWindow.SmallScroll Down:=-3
End Sub

 

[Joe4]

Why don't you try to implement the method I showed you above for the previous question.
Take a crack at it, that is the best way to learn these things!