Edit Macro to last active cell in column

[mpchambers]

Hello,

Below is part of a macro that I have been working on to automate a task for myself and others in my position. The issue with this current macro is that when I use the macro it only goes to a certain cell in the column. The reason why it does this is when I recorded the Macro that current document had an active cell to S268. I have highlighted the issues where I need the macro to go to the last active cell in the column.

Cells.Select
Cells.EntireColumn.AutoFit
Range("C2:N2").Select
Range(Selection, Selection.End(xlToLeft)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range("C2:N2").Select
Range(Selection, Selection.End(xlDown)).Select
Range("C2:N2").Select
Range(Selection, Selection.End(xlDown)).Select
Range("R1").Select
ActiveSheet.Range("$A$1:$R$268").AutoFilter Field:=14
Columns("R:R").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("R1").Select
ActiveCell.FormulaR1C1 = "Min Adj RU"
Range("T1").Select
ActiveCell.FormulaR1C1 = "Max Adj RU"
Range("R2").Select
ActiveCell.FormulaR1C1 = "=ROUNDUP(RC[-1],0)"
Range("R2").Select
Selection.AutoFill Destination:=Range("R2:R268")
Range("R2:R268").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("Q:Q").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("S2").Select
ActiveCell.FormulaR1C1 = "=ROUNDUP(RC[-1],0)"
Range("S2").Select
Selection.AutoFill Destination:=Range("S2:S268")
Range("S2:S268").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("T7").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = ""
Columns("R:R").Select
Selection.Delete Shift:=xlToLeft
Cells.Select
Cells.EntireColumn.AutoFit
Range("I10").Select
Range("R1").Select
Selection.AutoFilter
Selection.AutoFilter
Range("S1").Select
ActiveCell.FormulaR1C1 = "Total"
Range("S1").Select
Selection.AutoFilter
Selection.AutoFilter
Range("S2").Select
ActiveCell.FormulaR1C1 = "=(RC[-1]-RC[-12])*RC[-8]"
Range("S2").Select
Selection.AutoFill Destination:=Range("S2", Cells(LastRow, LastCol))
Range("S2", Cells(LastRow, LastCol)).Select
Selection.Style = "Currency"
Range("T9").Select
ActiveSheet.Range("$A$1:$S$268").AutoFilter Field:=19, Criteria1:="=$-", _
Operator:=xlOr, Criteria2:="=#VALUE!"
Range("S147").Select
ActiveSheet.Range("$A$1:$S$268").AutoFilter Field:=8, Criteria1:="-"
Range("F147:H147").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Replace What:="-", Replacement:="0", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("S:S").ColumnWidth = 10.43
ActiveSheet.Range("$A$1:$S$268").AutoFilter Field:=19
Range("P282").Select
ActiveSheet.Range("$A$1:$S$268").AutoFilter Field:=8
Range("S269").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-267]C:R[-1]C)"
Range("xldown").Select
Columns("S:S").ColumnWidth = 12.86
End Sub

Any help would be much appreciated.

Cordially,

Mike

 

[mpchambers]

Re: Macro - Edit Macro to last active cell in column

So the final code should look like this:

Sub MRDMacro()
'
' MRDMacro Macro
'


'
LastRow = Cells(Rows.Count, "R").End(xlUp).Row
Cells.Select
Cells.EntireColumn.AutoFit
Range("R1").Select
ActiveSheet.Range("A1:R1").AutoFilter Field:=14
Columns("R:R").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("R1").Select
ActiveCell.FormulaR1C1 = "Min Adj RU"
Range("T1").Select
ActiveCell.FormulaR1C1 = "Max Adj RU"
Range("R2").Select
ActiveCell.FormulaR1C1 = "=ROUNDUP(RC[-1],0)"
Range("R2").Select
Selection.AutoFill Destination:=Range("R2:R" & LastRow)
Range("R2:R" & LastRow).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("Q:Q").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("S2").Select
ActiveCell.FormulaR1C1 = "=ROUNDUP(RC[-1],0)"
Range("S2").Select
Selection.AutoFill Destination:=Range("S2:S" & LastRow)
Range("S2:S" & LastRow).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("T7").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = ""
Columns("R:R").Select
Selection.Delete Shift:=xlToLeft
Cells.Select
Cells.EntireColumn.AutoFit
Range("I10").Select
Range("R1").Select
Selection.AutoFilter
Selection.AutoFilter
Range("S1").Select
ActiveCell.FormulaR1C1 = "Total"
Range("S1").Select
Selection.AutoFilter
Selection.AutoFilter
Range("S2").Select
ActiveCell.FormulaR1C1 = "=(RC[-1]-RC[-12])*RC[-8]"
Range("S2").Select
Selection.AutoFill Destination:=Range("S2:S" & LastRow)
Range("S2:S" & LastRow).Select
Selection.Style = "Currency"
Range("T9").Select
ActiveSheet.Range("$A$1:$S$" & LastRow).AutoFilter Field:=19, Criteria1:="=$-", _
Operator:=xlOr, Criteria2:="=#VALUE!"
Range("S147").Select
ActiveSheet.Range("$A$1:$S$" & LastRow).AutoFilter Field:=8, Criteria1:="-"
Range("F147:H147").Select
Selection.Replace What:="-", Replacement:="0", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("S:S").ColumnWidth = 10.43
ActiveSheet.Range("$A$1:$S$" & LastRow).AutoFilter Field:=19
Range("P282").Select
ActiveSheet.Range("$A$1:$S$" & LastRow).AutoFilter Field:=8
Range("S269").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-" & LastRow - 1 & "]C:R[-1]C)"
Range("xldown").Select
Columns("S:S").ColumnWidth = 12.86
End Sub

 

[Joe4]

Re: Macro - Edit Macro to last active cell in column

So the final code should look like this:

Not quite. Read my last reply completely, especially the end.

 

[mpchambers]

Re: Macro - Edit Macro to last active cell in column

Thanks for all your help. The macro works percfectly.