Macro Help

[Dorbe1]

I created a macro that is not working properly. When I created it, I had data that utilized rows all the way down to 1501 in columns "J" and "K". Not all my files ae the same. So When I use the created macro, It still builds to row 1501 no matter what. I know there is a way to tell it to go to the end of that data, But when I edit it to do so, it errors out.

Here is the portion of the macro where it is going to row 1501:
Cells.Select
Cells.EntireColumn.AutoFit
Columns("J:K").Select
Selection.Delete Shift:=xlToLeft
Range("J2").Select
ActiveCell.FormulaR1C1 = "700"
Range("K2").Select
ActiveCell.FormulaR1C1 = "2330"
Range("J2:K2").Select
Selection.AutoFill Destination:=Range("J2:K1501")
Range("J2:K1501").Select
Columns("U:U").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("U2").Select
ActiveCell.FormulaR1C1 = "=RIGHT(RC[-2]+100,2)"
Range("U2").Select
Selection.AutoFill Destination:=Range("U2:U1501")
Range("U2:U1501").Select
Selection.Copy
Range("S2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("T2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Columns("U:U").Select
Selection.Delete Shift:=xlToLeft
ActiveWindow.ScrollColumn = 2

 

[Joe4]

Welcome to the Board!

A few things. You can reduce your code quite a bit by getting rid of most of the "Select" statements. They are usually the product of the Macro Recorder, but are usually unnecessary and slow the code down. It is usually not necessary to select a range before working on it.

In most cases, where you have one line end in "Select" and the next start with "Selection" or "ActiveCell", you can combine them together like this:

Range("U2").Select
ActiveCell.FormulaR1C1 = "=RIGHT(RC[-2]+100,2)"

becomes

Range("U2").FormulaR1C1 = "=RIGHT(RC[-2]+100,2)"

Regarding the last row, if you can identify some column we can use to locate the last row, we can find it dynamically.
For example, let's say that for every row with data, column B will be populated. So we can use column B to dynamically find our last row like this:

Dim lr as Long
lr = Cells(Rows.Count, "B").End(xlUp).Row

Then, we can apply our formula to that whole dynamic range at once.
So, for column U above, it would look like:

Range("U2:U" & lr).FormulaR1C1 = "=RIGHT(RC[-2]+100,2)"

You can also get rid of all the recorded screen scroll line, which look like this:

ActiveWindow.ScrollColumn = 2

that is just the Macro Recorder recording your scrolling around the screen, and is not needed at all.

So you should be able to apply these bits of information to your code to make it dynamic and clean it up a bit.

 

[Dorbe1]

Thank you, I understand all except where to put the formula to have it adjust the column down to the end of the data. In my macro, I only wish to have that done in columns J,K,T and U.

Below is the entire macro:

Sub File_Prep()
'
' File_Prep Macro
' File Prep
'

'
Cells.Select
Cells.EntireColumn.AutoFit
Columns("J:K").Delete Shift:=xlToLeft
Range("J2").Select.FormulaR1C1 = "700"
Range("K2").FormulaR1C1 = "2330"
Range("J2:K2").AutoFill Destination:=Range("J2:K1501")
Range("J2:K1501").Select
Columns("U:U").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("U2").FormulaR1C1 = "=RIGHT(RC[-2]+100,2)"
Range("U2").AutoFill Destination:=Range("U2:U1501")
Range("U2:U1501").Copy
Range("S2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("T2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Columns("U:U").Delete Shift:=xlToLeft
Range("AT1").FormulaR1C1 = "Day"
Range("P2").Select
Range(Selection, Selection.End(xlDown)).Copy
Range("AT2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Replace What:="M", Replacement:="1", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="M", Replacement:="1", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="T", Replacement:="2", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="T", Replacement:="2", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="W", Replacement:="3", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="R", Replacement:="4", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="F", Replacement:="5", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("J:J").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("J2").FormulaR1C1 = "=LEFT(RC[-1],5)"
Range("J2").AutoFill Destination:=Range("J2:J1501")
Range("J2:J1501").Copy
Range("I2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Columns("J:J").Delete Shift:=xlToLeft
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("W2:W1501" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A1:AT1501")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

End Sub

 

[Michael M]

Like this

I created a macro that is not working properly. When I created it, I had data that utilized rows all the way down to 1501 in columns "J" and "K". Not all my files ae the same. So When I use the created macro, It still builds to row 1501 no matter what. I know there is a way to tell it to go to the end of that data, But when I edit it to do so, it errors out.

Here is the portion of the macro where it is going to row 1501:
Cells.Select
Cells.EntireColumn.AutoFit
Columns("J:K").Select
Selection.Delete Shift:=xlToLeft
Range("J2").Select
ActiveCell.FormulaR1C1 = "700"
Range("K2").Select
ActiveCell.FormulaR1C1 = "2330"
Range("J2:K2").Select
Selection.AutoFill Destination:=Range("J2:K1501")
Range("J2:K1501").Select
Columns("U:U").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("U2").Select
ActiveCell.FormulaR1C1 = "=RIGHT(RC[-2]+100,2)"
Range("U2").Select
Selection.AutoFill Destination:=Range("U2:U1501")
Range("U2:U1501").Select
Selection.Copy
Range("S2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("T2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Columns("U:U").Select
Selection.Delete Shift:=xlToLeft
ActiveWindow.ScrollColumn = 2

his ??

 

[Michael M]

OOps, hit the wrong button....

Dim lr As Long
lr = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
Cells.EntireColumn.AutoFit
Columns("J:K").Delete Shift:=xlToLeft
Range("J2").Value = "700"
Range("K2").Value = "2330"
Range("J2:K2").Copy Range("J3:K" & lr)
Columns("U:U").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
With Range("U2:U" & lr)
.FormulaR1C1 = "=RIGHT(RC[-2]+100,2)"
.Value = .Value
End With

 

[Dorbe1]

Thank you, Have to say I am learning as I go here. My question is where would you insert the above code in the macro I provided. I tried putting in in where I thought it should go but keep getting errors. I get no errors when I remove all the .Select and ActiveCell stuff you suggested. Really sped up the process, thanks.