How do you assign a range for formatting when combined with resizing?

[vbaNumpty]

Trying to resize a range and apply borders around it, but I get application/object defined error.

What is the proper way to code the following?

        With Range(addMe.Resize(0, 17)).Borders
            .LineStyle = xlContinuous
            .Weight = xlThin
        End With

addme is a range that is assigned

 

[Fluff]

Just use

With addMe.Resize(0, 17).Borders

 

[vbaNumpty]

Just use

With addMe.Resize(0, 17).Borders

gives me application/object defined error

 

[Fluff]

Do you want to change the number of rows being formatted.

 

[vbaNumpty]

Do you want to change the number of rows being formatted.

Yes, in some instances the formatting is more than one row. It is based on the number of line items tied to the customer.


The addme is set to one cell

Set addMe = dshBoard.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)

that is then resized and merged

addMe.Resize(x - 1).Merge

the resize is dependent on how many line items are in the order.

I figured when you select a merged section in excel and move to the right with shift it selects all the rows that make up the initial merged selection.

I'm guessing vba doesn't like that and I have to specify the number of rows somehow?

 

[Fluff]

that is then resized and merged

In that case I'm out. Merged cells are an abomination & should be avoided like the plague.

 

[vbaNumpty]

Do you want to change the number of rows being formatted.

I fixed the object error by assigning the same row variable as before (x-1)

but now I get 438 error, Object doesn't support this property or method for :

.LineStyle = xlContinuous

I am just trying to apply the all borders formatting.

 

[vbaNumpty]

In that case I'm out. Merged cells are an abomination & should be avoided like the plague.

Figured it out, screwed up syntax forgetting the .borders argument after I fixed the row argument error.

Thanks for your help! Your assistance unlocked the key for me to figure out how to work with the plague

Solution:

 With addMe.Resize(x - 1, 17).Borders
            .LineStyle = xlContinuous
            .Weight = xlThin
End With