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]

 

[Fluff]

How about

   Range("G2:G" & Range("A" & Rows.Count).End(xlUp).Row).FormulaR1C1 = "=RIGHT(RC[-6],LEN(RC[-6])-FIND(""-"",RC[-6],1))"

 

[Justinian]

That did not do anything. I just need to modify this portion:

Selection.AutoFill Destination:=Range("G2:G90")
Range("G2:G90").Select

 

[Fluff]

Assuming that you are putting the formula in G2, then my code replaces all of yours and will fill the formula down to the last row of col A.

 

[Justinian]

What part of my code do I replace with yours?

 

[Fluff]

This part

[FONT=Verdana]Selection.FormulaR1C1 = "=RIGHT(RC[-6],LEN(RC[-6])-FIND(""-"",RC[-6],1))"
    Range("G2").Select
    Selection.AutoFill Destination:=Range("G2:G79")[/FONT]

 

[Justinian]

It is not working. How about I post my worksheet so you can see it? I should have done that earlier.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]https://app.box.com/s/8eke1c83ca7kihaxqoflp3zlwepy0mm3

[/FONT]

 

[Fluff]

The reason it doesn't work is that col A does not contain any values with a - in them, therefore the formula returns #VALUE!

 

[Justinian]

When I do put items that contain a "-" look what happens below row 79:

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]https://app.box.com/s/ufphfie4vs55ct936ts1up5ildim1mlj[/FONT]

 

[Fluff]

The formula in col G returns

Book1
	A	B	C	D	E	F	G
79	3502K	037 Tower 2Bd Premium *******	09/05/2019		11/13/2019	Completed: 09/30/19 Scheduled: 09/09/19 remove radiators# 924583-1 and A/C REMOVAL #924600-1	#VALUE!
80	42-4207L	036 Tower 2Bd Premium	09/14/2019		11/13/2019	In progress: Scheduled: 09/17/19 SM TO	4207L
81	33-3300J	036 Tower 2Bd Premium	10/01/2019		11/26/2019	In progress: Scheduled: 10/02/19 REMOVE RADIATORS #928063-1	3300J
82	46-4608B	036 Tower 2Bd Premium	09/29/2019		11/27/2019	In progress: REMOVE RADIATORS #927751-1	4608B
83	46-4608B	09/29/2019	11/27/2019	In progress: REMOVE RADIATORS #927751-1			4608B
Make Ready Board

Cell Formulas
Range	Formula
G79	=RIGHT(A79,LEN(A79)-FIND("-",A79,1))

Which is correct.