Modifying VBA Code to Include All Used Rows (Dynamic, Not Static)

[Justinian]

I import daily data into Excel for a morning report and the data does not always take up the same amount of rows. One day, it could go down to row 79 but the next day, it could go down to row 90. How can I modify the VBA below so that the code looks at all used rows instead of this static range?

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif] Selection.FormulaR1C1 = "=RIGHT(RC[-6],LEN(RC[-6])-FIND(""-"",RC[-6],1))"
Range("G2").Select
Selection.AutoFill Destination:=Range("G2:G79")
Range("G2:G79").Select
Selection.Copy[/FONT]

 

[Justinian]

The code you provided puts that formula into column G.

Here is the full code from my workbook:

' Inserts RIGHT formula into G2, applies result to A2, and deletes leftovers in column G
Range("G2").Select
Selection.FormulaR1C1 = "=RIGHT(RC[-6],LEN(RC[-6])-FIND(""-"",RC[-6],1))"
Range("G2").Select
Selection.AutoFill Destination:=Range("G2:G79")
Range("G2:G79").Select
Selection.Copy
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("G:G").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("A1").Select

 

[Fluff]

The code you provided puts that formula into column G.

So does your code, so what's the problem/question?

 

[Justinian]

No, my code puts the RIGHT formula into column G then copies the results down to row 79. Then it pastes values into column A and deletes the contents of column G.

Your code only affects rows 1-79 and then does weird things to 80 and below.

 

[Fluff]

That's because you have not changed your code to what I suggested & therefore only works on rows2 to 79.
Replace the last section of your code with


    'Centers all data in column A, C, D, and E
    Range("A:A,C:E").EntireColumn.HorizontalAlignment = xlCenter
    
    With Range("G2:G" & Range("A" & Rows.Count).End(xlUp).Row)
      .FormulaR1C1 = "=RIGHT(RC[-6],LEN(RC[-6])-FIND(""-"",RC[-6],1))"
      .Copy
      Range("A2").PasteSpecial xlPasteValues
      .ClearContents
    End With
    Range("A1").Select

End Sub

 

[Justinian]

I swear I inserted your code but I must have screwed with another line.

Thank you very much! This was very helpful.

 

[Fluff]

Glad to help & thanks for the feedback