Filling in a range of cells with the same value - fastest method RRS feed

  • Question

  • The current and basic method I am using is this

    Dim t As Integer 
    For t = 2 To 10
            Cells(t, 1).Value = CampaignName

    NB CampaignName is a string

    I want to have the optimum solution.

    Any help would be appreciated, thanks

    Wednesday, May 24, 2017 9:07 AM

All replies

  • Hi VBNovice01,
    This is the most efficient way to push one value to a range of cells:
    Range("A2:A10").Value = CampaingName
    Wednesday, May 24, 2017 9:47 AM
  • Hi Jan, thanks for the reply

    what if my range is not fixed. Lets say from 2 to n?Would this work

    Range( Cells (2,1) , Cells ( n, 1 ) )  = CampaignName

    • Edited by VBNovice01 Wednesday, May 24, 2017 10:39 AM
    Wednesday, May 24, 2017 10:39 AM
  • Hi VBNovice01,
    Yes, that would work. Note that this operates on the active worksheet.
    If you would want this to work on -say- Sheet3 you would need:
    With Worksheets("Sheet3")
        .Range(.Cells (2,1), .Cells ( n, 1 ) ).Value  = CampaignName
    End With
    Note that I added .Value too, I don't like leaving things up to VBA to decide,
    so I tell it up front which property I want it to address rather than depending
    on VBA to use the default property of the object (in case of the range object
    this is its value).
    Wednesday, May 24, 2017 11:02 AM