Setting a range of cells

[Paleo]

Hi all,

I have this code:

Dim cl As Range
For Each cl In [B1, D1,H1]

It works fine, but I need something that points to the last cell, something like:

Dim cl As Range
For Each cl In [replace(Range("B65536").End(xlUp).Address,"$",""), replace(Range("D65536").End(xlUp).Address,"$",""), replace(Range("H65536").End(xlUp).Address,"$","")]

or even better

Dim cl As Range, n As Long
n = Range("A65536").End(xlUp).Row
For Each cl In ["B" & n, "D" & n, "H" & n]

Of course the two last ones didnt work.

Any help???

[mvidas]

Hello,

First off, just as a future tip, rather than use replace on an address and change the $ signs to nothing, you can just use .Address(0,0) to return the local address (A1 not $A$1).

As for your question, if you want it to return the last cells in those given columns, use

For Each cl In Union([b65536].End(xlUp), [d65536].End(xlUp), [h65536].End(xlUp))

But if you want it to return the cells in those column but in the last row of A (its possible they're different), you could use:

n = Range("A65536").End(xlUp).Row
For Each cl In Union(Range("B" & n), Range("D" & n), Range("H" & n))

Matt

[Paleo]

Hi Matt,

didnt work yet. The first two buttons are getting one over the other.

The code I am using now:

n = Range("A65536").End(xlUp).Row + 5
    For Each cl In Union(Range("B" & n), Range("B" & n + 5), Range("H" & n)) 'update this range to place buttons
        i = i + 1
        Set myCmdObj = TargetSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
        Left:=cl.Left + 1, Top:=45, Width:=202.5, Height:=32.25)

[mvidas]

Hmm, it could be because you're using Top:=45

Try changing it to Top:=cl.Top (or cl.Top+1)

[Paleo]

Hi Matt,

great idea!!

After that change the buttons still are getting over the data so I have made some more changes and got it working just fine.

This is my final code:

n = Range("A65536").End(xlUp).Row + 2
For Each cl In Union(Range("B" & n), Range("B" & n + 5), Range("J" & n)) 'update this range to place buttons
i = i + 1
Set myCmdObj = TargetSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
Left:=cl.Left + 1, Top:=cl.Top + 1, Width:=202.5, Height:=32.25)