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

 

[Joe4]

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

Welcome to the Board!

You can find the last row like this:

[COLOR=#333333]LastRow = Cells(Rows.Count,"R").End(xlUp).Row

Then you can replace references like this:

Range("R2:R268")

with this:
[/COLOR]

[COLOR=#333333]Range("R2:R" & LastRow)[/COLOR]

and you can replace references like this:

[COLOR=#333333]ActiveCell.FormulaR1C1 = "=SUM(R[-267]C:R[-1]C)"[/COLOR]

with this:

[COLOR=#333333]ActiveCell.FormulaR1C1 = "=SUM(R[-" LastRow - 1 & "]C:R[-1]C)"[/COLOR]

 

[mpchambers]

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

what about a formula like this?

ActiveSheet.Range("$A$1:$S$268").AutoFilter Field:=19, Criteria1:="=$-", _
Operator:=xlOr, Criteria2:="=#VALUE!"

 

[Joe4]

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

Don't let the "$"s throw you off. You just do it the same way I showed you above, replacing the row number with row variable, i.e.
change

[COLOR=#333333].Range("$A$1:$S$268")[/COLOR]

to

[COLOR=#333333].Range("$A$1:$S$" & LastRow)[/COLOR]

 

[mpchambers]

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

Sorry for all of the noob questions! I have one last question...hopefully.

For this Formula: ActiveCell.FormulaR1C1 = "=SUM(R[-" LastRow - 1&"]C:R[-1]C)" causes an error popup that states "Compile Error: Expected: End of Statement". How do i fix this?

Thank you again for all your help. I really appreciate it greatly.

 

[Joe4]

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

Whoops, I forgot am apersand, it should be:

[COLOR=#333333]ActiveCell.FormulaR1C1 = "=SUM(R[-" & LastRow - 1 & "]C:R[-1]C)"[/COLOR]

Also, be sure to maintain the spacing in the formula - it is important!
Basically, the key thing to remember is anything between double-quotes is literal text to be returned, and that not between them are variables for which we want the values they represent returned.

 

[mpchambers]

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

Perfect! So everything works up until this point of the formula:

ActiveSheet.Range("$A$1:$S$" & LastRow).AutoFilter Field:=19, Criteria1:="=$-", - Pops up an error box that says expected named parameter
Operator:=xlOr, Criteria2:="=#VALUE!" and this one pops up a expected expression statement.

 

[Joe4]

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

Did you forget the underscore at the end of the row?

ActiveSheet.Range("$A$1:$S$" & LastRow).AutoFilter Field:=19, Criteria1:="=$-", [COLOR=#ff0000]_[/COLOR]
    Operator:=xlOr, Criteria2:="=#VALUE!"

 

[mpchambers]

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

Yes, I had forgotten the under-score. So when I begin to run the macro on the excel sheet...once i hit this part of the macro:

ActiveSheet.Range("$A$1:$R$" & LastRow).AutoFilter Field:=14

I get an error message that states: Run Time Error '1004': Method Range of Object_Global failed.

 

[Joe4]

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

What is the value of LastRow when you get the error?
That row isn't already filtered/hidden, is it?