Union not hardcoded

[Hawkin]

Hello,

I want to fill multiple Cells at once that are 5 columns off from each other, so I use

Union(Cells(2, i), Cells(2, i + 5), Cells(2, i + 10), ...) = "Text"

But the amount of cells is variable. Everytime we need a new set of columns, I need to change the code to implement them.

What I would like is to "fill" or enhance the union with all needed Cells with a For..Next loop, so that I never need to hardcode the cells anymore. Like saving the union range into a variable.

This approach shows the direction I want this to go

For i = 1 to lastcolumn Step 5[INDENT]UnionVariable = UnionVariable + Union(Cells(2, i))[/INDENT]
Next
'Now I have automatically gotten all cells and fill them at once
UnionVariable = "Text"

I have tried different methods with filling cells one by one, but unfortunately this is a performance killer as it took my code several seconds. With filling all cells at once using union it takes less than 1 second which is what I need.

Thanks in advance

 

[RickXL]

Hi.

I have put two ideas in here. Firstly, I have reformatted your original idea using a Range variable and concatenating the ranges in the way you suggested.

Secondly I have added : Application.ScreenUpdating = False/True.
This will stop Excel updating the fields as you go round your loop that updates the cells separately without using UNION. That may be all you need. Basically, it is a different way of updating all the cells at once. Whether it works will depend on whether the time was being consumed by Excel repeatedly updating the screen or by running round your loop.

Sub xxxx()
    Application.ScreenUpdating = False
    Dim UnionVariable  As Range
    With Worksheets("Sheet1")
        lastcolumn = 16
        Set UnionVariable = .Cells(2, 1)
        For i = 6 To lastcolumn Step 5
            Set UnionVariable = Union(UnionVariable, .Cells(2, i))
        Next
        'Now I have automatically gotten all cells and fill them at once
        UnionVariable = "Text"
    End With
    Application.ScreenUpdating = True
End Sub

 

[Hawkin]

Oh I didn't know that it's really possible to set such a UnionVariable. Perfect Solution, thank you!

Note: Set'ing the UnionVariable before looping is obligatory for it to work.

 

[RickXL]

No problem and thanks for the feedback.

Regards,