repeat cell value x times

I am trying to apply a text value to a variable number of cells in one column. The number of times to be repeated is dictated by the numeric value in a cell.

I.e.
cell D1 text is "C"
cell D2 value is "23"
cell E1 text is "E"
cell E2 value is "5"

I have a table containing 33 rows (range A14:F47 including header row). I need a macro to apply the letter "C" in "23" cells starting at cell D15 and then apply the letter "E" in the next "5" cells. This example should result in 28 cells being filled. So, the range to be filled in this example will be D15 through to D42. I already have a macro to hide any unused rows in the table.

The values in cells D2 and E2 will change depending on other data entered in the worksheet. I.e. D2 could be 11 and E2 could be 4 and the remaining unused rows get hidden.

Very beginner level , you should try ‼

PHP Code: 

Sub Demo()
    With Sheet1
                             .[D15].Resize(.[D2].Value).Value = .[D1].Value
        .Cells(15 + .[D2].Value, 4).Resize(.[E2].Value).Value = .[E1].Value
    End With
End Sub 


Do you like it ? So thanks to click on bottom left star icon « Add Reputation » !

My code is exactly the reflect of your explanation ! Of course with an error in your explanation …

Your code uses not necessary object variables (useless for once !) and you forgot to free them !
At a bigger scale, it's a waste of time for execution and a waste of system ressources, so not so good …

And you forgot the dots in With block !

Originally Posted by Julez74

Thanks for the suggestion but your solution didn't quite work.

I mocked up your file and put that code in and it seemed to work fine. What didn't work?

Your code should have worked, it made sense. I tried your code again and it still came up with an error to either end or debug. But, this time I actually read the error message (should have done this to begin with, I know). The error was caused by sheet protection. When I removed the sheet protection, your code worked. I have cells with formulas that are protected to stop users from deleting or changing them accidentally. The only reason my version worked was because I tested it while sheet protection was off. Lesson learned.