Required help in Marco (fill till last row)

[jgrptl]

hi Guys, I am relatively new to Marco. I am trying to create marco, but not able to only autofill till last raw. I have created coding, but it is upto 137 row. I really want to auto fill till last row which have value in previous column. my coding: Sub Macro1()
'
' Macro1 Macro
'
'
ActiveCell.FormulaR1C1 = "Delivery"
Selection.AutoFill Destination:=Range("L2:L137")
Range("L2:L137").Select
Range("M2").Select
ActiveCell.FormulaR1C1 = "Australia Wide"
Range("M2").Select
Selection.AutoFill Destination:=Range("M2:M137")
Range("M2:M137").Select
ActiveWindow.SmallScroll Down:=3
Range("N21").Select
End Sub

 

[Michael M]

Assuming you list is in Column"K", try

Sub MM1()
Dim lr As Long
lr = Cells(Rows.Count, "K").End(xlUp).Row
Range("L2:M" & lr).Value = Array("Delivery", "Australia Wide")
Range("N21").Select
End Sub

 

[jgrptl]

thanks for support, but I want to modify formula in my coding, as I have more complex and last row is always changing. it will be great if you modify ONLY my formula, which will help me to understand coding well.

 

[Michael M]

You don't have any formulas in your code....
And the code supplied adjust for the last row in Col "K" regardless of where that last row is !
If this doesn't help, you will need to provide a detailed explanation of what you are trying to do !

 

[jgrptl]

hi Michael,

when I run a report from my system, it will give me numbers of different parameters. some of them I need & some need to remove. rest I need to update. I am trying to create marco which will help me to avoid repetitive work.

in blow my coding, you notice I have limitation up to 50 rows, I want to do It up to last cell. everytime numbers of rows are different in different report, but they are same in each report.

below is my coding:

Sub Dell_System()
'
' Dell_System Macro
' Description Fix for Dell System
'
'
Columns("A:C").Select
Range("C1").Activate
Selection.Delete Shift:=xlToLeft
Columns("B:E").Select
Selection.Delete Shift:=xlToLeft
Range("D2").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[-1], FIND(""</b>"", RC[-1])-1)"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D50")
Range("D2:D50").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.Replace What:="<b>", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("E2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Dell"
Selection.AutoFill Destination:=Range("E2:E50")
Range("E2:E50").Select
Range("F1").Select
ActiveCell.FormulaR1C1 = "Vendorlogoid"
Range("F2").Select
ActiveCell.FormulaR1C1 = "Dell"
Selection.AutoFill Destination:=Range("F2:F50")
Range("F2:F50").Select
Columns("G:Q").Select
Selection.Delete Shift:=xlToLeft
Columns("H:J").Select
Selection.Delete Shift:=xlToLeft
Columns("I:N").Select
Selection.Delete Shift:=xlToLeft
Range("I2").Select
ActiveCell.FormulaR1C1 = "9"
Selection.AutoFill Destination:=Range("I2:I50")
Range("I2:I50").Select
Range("J2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-2]<2901, ""15"", IF(RC[-2]>2904, ""15"", ""25""))"
Range("J2").Select
Selection.AutoFill Destination:=Range("J2:J50")
Range("J2:J50").Select
ActiveWindow.SmallScroll Down:=0
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("L2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=IF(ISNUMBER(SEARCH(""PASS"", RC[-5], 1)), ""2"", ""200"")"
Range("L3").Select
Columns("G:G").ColumnWidth = 22.43
Range("L2").Select
Selection.AutoFill Destination:=Range("L2:L50")
Range("L2:L50").Select
ActiveWindow.SmallScroll Down:=9
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("K2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"`` This can be purchased separately from DELL ( EXCEPT SECONDS PRODUCT & PERIPHERALS ) - see any Dell Sale Overview for instructions ``" & Chr(13) & "" & Chr(10) & ""
Range("P2:P3").Select
Range("P3").Activate
ActiveCell.FormulaR1C1 = _
"`` This can be purchased separately from DELL ( EXCEPT SECONDS PRODUCT & PERIPHERALS ) - see any Dell Sale Overview for instructions ``" & Chr(10) & ""
Cells.Select
ActiveWindow.SmallScroll Down:=-15
Selection.RowHeight = 13
Range("K2").Select
ActiveCell.FormulaR1C1 = _
"=IF(SEARCH(""200"", RC[28]), ""No Warranty Applies"")"
Range("K2").Select
ActiveCell.FormulaR1C1 = "=IF(SEARCH(""200"", RC[1]), ""No Warranty Applies"")"
Range("K2").Select
Columns("K:K").ColumnWidth = 20.71
Selection.AutoFill Destination:=Range("K2:K50")
Range("K2:K50").Select
Selection.Copy
Application.CutCopyMode = False
Selection.Copy
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("K9").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "#VALUE!"
Columns("K:K").Select
Selection.Replace What:="#VALUE!", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("M:AH").Select
Selection.Delete Shift:=xlToLeft
Columns("P:U").Select
Selection.Delete Shift:=xlToLeft
Range("M2").Select
ActiveCell.FormulaR1C1 = "Computers & Electronics"
Range("N2").Select
ActiveCell.FormulaR1C1 = "Computers"
Range("M2:N2").Select
Selection.AutoFill Destination:=Range("M2:N50")
Range("M2:N50").Select
Selection.FillDown
Range("O2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-7]=2903, ""Desktops"", IF(RC[-7]=2902, IF(ISERR(SEARCH(""Tablet"", RC[-11])), ""Laptops"", ""Tablets""), ""Monitors""))"
Range("O2").Select
Selection.AutoFill Destination:=Range("O2:O50")
Range("O2:O50").Select
ActiveWindow.SmallScroll Down:=18
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=-21

MsgBox "Please update 'Warranty & WarrantyContact' for recondition Monitors"

 

[Michael M]

This area of code

Range("D2").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[-1], FIND("""", RC[-1])-1)"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D50")

can be modified to

Range("D2:D" & lr).Formula = "=LEFT(C2, FIND("""", C2)-1)"

but you will need to insert these 2 lines at the beginning of the macro

Dim lr as long
lr = cells(rows.count,"C").end(xlup).row

There are also a few more changes that can be made
Remove references of .Select .Selection
So this, for instance

Columns("A:C").Select
Range("C1").Activate
Selection.Delete Shift:=xlToLeft

will become this

Columns("A:C").Delete Shift:=xlToLeft