Simply trying to paste the same string into a range of cells with Excel VBA

Question

I need to paste the same string value into every row of an excel spreadsheet. I have defined the string within a loop, via my vba code:

    StickName = Left(myfile, 9)
    ActiveCell.FormulaR1C1 = StickName

It seems so simple to fill each row with the same value and I could do this via looping, writing into each cell until I reach the bottom row of the data, but there must be a more elegant solution. Is there a way to name a range (which would be a single column of data, from row x to y, within my loop) and fill an entire range with the same string? Then re-define the range and the string value for each iteration of the loop? I have searched on fill range with value and other similar searches and scoured the answers, but I don't see anything that addresses what I'm trying to do. Thank you for your help!

Answer 1

Define a range and then set the value of the range.

Dim r As Range
Dim s As String
s = "Hello World"
Set r = Sheets(1).Range("A1:A15")
r.Value = s

Answer 2

You don't need any loops. Say we want to fill rows 7 through11 with some value:

Sub liroch()
   Dim StickName As String, myfile As String

   myfile = "abcdefghijklmnop"
   StickName = Left(myfile, 9)
   Rows("7:11").Value = StickName
End Sub

Answer 3

You can declare only your variable StickName and use this code:

StickName = Left(myfile, 9)
Range(Cells(startRow, StartColumn),Cells(LastRow, LastColumn)) = StickName