Trouble Figuring out How to replace Cells in a Range with Specific Text - Excel VBScript

I must be having a brain fog at this point because I am certain this is easy开发者_开发知识库 to do, and in fact I have managed to create other functions that are a bit more complicated for this project.

Anyway, what I am trying to do. I have a sheet (inventory-data) and in column 1, it lists a company name, which is a same for all the rows. i.e. each of the 1900 or so rows have companyname in the first cell.

Now, while the data will always be the same at each application, the number of rows will change.

So, I need a function that will first determine what the last row of data is in the range, and then change all of the cells in column one of each record to name_company. The company names will always be the same so I can staticly assign them. Here is what I have that does not work.

I was able to get it to work another way, but it would replace text all the way down to the very last row of the worksheet, way beyond where the data stops.

Thanks!

Sub changeCompany() 'Changes company name as pulled from Agemni into proper ETA format

Dim myCell As Range
Dim RngToChange As Range 'The range of cells that need to be changed
Dim LastRow As Long 'Declare variable to help determine the last row on a variable length worksheet
Dim i As Integer

With Worksheets("inventory-data") 'set the range to change
    Set RngToChange = .Columns(1)
End With

LastRow = Worksheets("inventory-data").UsedRange.Rows.Count 'Have Excel determine what the last row is.

For i = LastRow To 1 Step -1
    RngToChange.Cells.Value = "name_company"
Next i

End Sub

I've always had more success with [SomeCellOrRange].CurrentRegion.Rows.Count e.g:

Range("A1").CurrentRegion.Rows.Count

UsedRange looks for any use of cells, not limited to a continuous tabular block. It also sometimes needs you to re-save the workbook before it will properly shrink after you have eliminated some rows.