Edit Macro to last active cell in column

M

mpchambers

New Member
Joined
Aug 30, 2016
Messages
11
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
 
Sort by date Sort by votes
Re: Macro - Edit Macro to last active cell in column

When I get the error it stops at R2. The column is not filtered or hidden. Here is the updated version of the Macro highlighted problem area:

Sub MRDMacro()
'
' MRDMacro Macro
'


'
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$" & LastRow).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
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$" & 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
 
Upvote 0

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Re: Macro - Edit Macro to last active cell in column

So, are you saying that LastRow is equal to 2 at that point?
What does the data look like on those first two rows, especially, what is in column N?

By the way, you don't have any merged cells or protected cells, do you?
 
Last edited:
Upvote 0
Re: Macro - Edit Macro to last active cell in column

I removed some unnecessary items from the code, but at this point, nothing has been done to the sheet except to run the macro. Nothing is hidden, merged, or protected. The code stops at R2 and doesn't run. The last cell in column R is R212. The LastRow value will vary each time I need to run the macro.

Sub MRDMacro()
'
' MRDMacro Macro
'


'
Cells.Select
Cells.EntireColumn.AutoFit
Range("R1").Select
ActiveSheet.Range("$A$1:$R$" & LastRow).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
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$" & 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
 
Upvote 0
Re: Macro - Edit Macro to last active cell in column

I can't really say without seeing your data.
I do not see anything inherently wrong with that line.
 
Upvote 0
Re: Macro - Edit Macro to last active cell in column

Not answering your question directly, but you can find the last row & column in a sheet :-

LastColumn = ActiveSheet.Cells.SpecialCells(xlLastCell).Column
LastRow = ActiveSheet.Cells.SpecialCells(xlLastCell).Row
 
Upvote 0
Re: Macro - Edit Macro to last active cell in column

How do I post my data?
Click to expand...
You can post it to a file sharing site and provide a link.
 
Upvote 0
Re: Macro - Edit Macro to last active cell in column

The issue is you are trying to use the LastRow calculated variable, but haven't actually included that calculation in your code!
You need to calculate it in your code before you use it!
 
Upvote 0
Re: Macro - Edit Macro to last active cell in column

So if you just add the calculation to the top, it will get past that error:
Code: 
LastRow = Cells(Rows.Count, "R").End(xlUp).Row

However, you have another error near the bottom of your code:
Code: 
Range("xldown").Select
Unless you have a range specifically named "xldown", you will get an error.
Not sure what you are trying to do here, but since the only row after this sets the width of column S, I think this erroneous line of code can be removed completely (as it doesn't really seem to serve any purpose).
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
How to get my macro to stop at the last recognized line
Replies
6
Views
250
Coco1
C
L
  • Question
Variable Rows, Help Speeding Up
Replies
4
Views
331
kevin9999
K
L
  • Solved
VBA remove leading zeros
2
Replies
14
Views
1K
Joe4
Joe4
T
  • Question
ActiveSheet. Paste error
Replies
1
Views
58
RoryA
RoryA
C
  • Question
Workday in VBA
Replies
1
Views
348
Joe4
Joe4

Forum statistics

Threads
1,215,528
Messages
6,125,342
Members
449,218
Latest member
Excel Master

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top