AutoFill to the last row

[bobrandom123]

I created a macro utilizing Excel 2007 in order to automate the process of using the TRIM function in column S to trim strings found in Column H and then AutoFill the entire column:

Sub TrimFileName()
Range("S2").Select
ActiveCell.FormulaR1C1 = "=TRIM(MID(RC[-11],24,25))"
Range("S2").Select
Selection.AutoFill Destination:=Range("S2:S65536")
Columns("S:S").EntireColumn.Autofit

End Sub

I am new to programming and I want to use macros in order to parse my data faster at work. Since the amount of data varies from sheet to sheet, how can I programatically get the range to go to the last row in the worksheet instead of it going to 65536?

 

[VoG]

Try

Sub TrimFileName()
Dim LR As Long
LR = Range("H" & Rows.Count).End(xlUp).Row
Range("S2").FormulaR1C1 = "=TRIM(MID(RC[-11],24,25))"
Range("S2").AutoFill Destination:=Range("S2:S" & LR)
Columns("S").EntireColumn.AutoFit
End Sub

 

[bobrandom123]

Thanks!!!!

 

[VoG]

You are welcome. In fact it could be simplified

Sub TrimFileName()
Dim LR As Long
LR = Range("H" & Rows.Count).End(xlUp).Row
With Range("S2")
    .FormulaR1C1 = "=TRIM(MID(RC[-11],24,25))"
    .AutoFill Destination:=Range("S2:S" & LR)
    .EntireColumn.AutoFit
End With
End Sub

 

[bobrandom123]

That does simplify things! How could I integrate the TrimFileName() macro with a copy/paste as values solution? Currently I have this code that removes the formula for the TRIM function:

Sub RemoveFormula()
    Columns("S:S").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("S1").Select
    Application.CutCopyMode = False
End Sub

 

[MARK858]

Try

Sub TrimFileName()
Dim LR As Long
LR = Range("H" & Rows.Count).End(xlUp).Row
With Range("S2")
    .FormulaR1C1 = "=TRIM(MID(RC[-11],24,25))"
    .AutoFill Destination:=Range("S2:S" & LR)
    .EntireColumn.AutoFit
End With
Range("S2:S" & LR).Value = Range("S2:S" & LR).Value
End Sub

 

[bobrandom123]

Thanks! This is really helping me out! I appreciate it