disappearing border

yky · Jul 5, 2011

I have column q's and also r's borders manually set to xlMedium. Then, I have vba code insert four columns to r, set their border, and manually delete the newly inserted columns (for testing purpose). Surprisingly, the border between q and r is gone.

I deleted the vba codes that deals with border and added them back one by one and found that once the following code in red is executed, the border between q and r is gone when I manually delete the newly inserted columns. (I do have code to set the edge border to xlMedium right after the code in red.)

What went wrong here and how do I keep the border between column q and r to xlMedium after I delete the newly inserted columns?

------------------------------------------------------------------
'cells R3, S3, T3, U3 are merged. Four columns are inserted with one
'insert command

Columns("R:R").Select
Selection.Insert Shift:=xlToRight

Selection.NumberFormat = "0"
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

Range([r6], [q65536].End(xlUp).Offset(0, 4)).Select
Selection.NumberFormat = "0"
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone

With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With

With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With

With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With

With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With

Scott Huish · Jul 5, 2011

You shouldn't need anywhere near that amount of code. Does this do what you want?

Code:

Sub test()
Columns("R:R").Insert
With Range("Q" & Rows.Count).End(xlUp).Resize(, 4)
    .HorizontalAlignment = xlCenter
    .Borders.Weight = xlMedium
End With
End Sub

AlphaFrog · Jul 5, 2011

If you add a right-side border to the cells in column Q, you then could delete column R and the right-side border in column Q will remain.

If you add a border to the left-side of column R and then delete column R, then that border is deleted with column R. No border between Q and R.

yky · Jul 6, 2011

HOTPEPPER said:
You shouldn't need anywhere near that amount of code. Does this do what you want?

Code:

Sub test() Columns("R:R").Insert With Range("Q" & Rows.Count).End(xlUp).Resize(, 4) .HorizontalAlignment = xlCenter .Borders.Weight = xlMedium End With End Sub

Thank you for your reply. But the code inserted cells without inside borders.

yky · Jul 6, 2011

AlphaFrog said:
If you add a right-side border to the cells in column Q, you then could delete column R and the right-side border in column Q will remain.

That is what I thought but that is not what happened. I manually added right-side border to column Q and left-side border to column R. After running the code, there is no border between Q and R. But if I used Excel's insert function (in the menu bar) to insert then delete the columns, the border stayed there. Thus, something in the code must have erased the border. But the code does nothing to column Q, does it? How come Q's right-side border is gone? That is the mystery I have yet to figure out.

AlphaFrog · Jul 6, 2011

This is what I think is happening.

Manual:
You add a right-side border to Q
Insert a column at R (border seen on left and right side of R)
You delete R and border on right-side of Q remains.

Macro:
Column Q has an existing right-side border
Macro Inserts a column at R
Macro formats borders around R which overwrites the column Q right-side border
Macro deletes column R
Column Q right-side border is gone

This part of your code is changing the Column Q right-side border

Code:

    With Selection.Borders([COLOR="Red"]xlEdgeLeft[/COLOR])
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With

You can't have both a right-side column Q border AND a separate left-side column R border.

yky · Jul 6, 2011

AlphaFrog said:
This is what I think is happening.

Manual:
You add a right-side border to Q
Insert a column at R (border seen on left and right side of R)
You delete R and border on right-side of Q remains.

Macro:
Column Q has an existing right-side border
Macro Inserts a column at R
Macro formats borders around R which overwrites the column Q right-side border
Macro deletes column R
Column Q right-side border is gone

This part of your code is changing the Column Q right-side border

Code:

With Selection.Borders([COLOR="Red"]xlEdgeLeft[/COLOR]) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlMedium End With

You can't have both a right-side column Q border AND a separate left-side column R border.

Thank you for the reply.

I commented out that section of the code and the border between column Q and R still disappeared.

This time, I was careful in deleting the columns. Before deleting, I checked the border between column Q and R and it was medium and R and U thin. After I deleted column R, the border between Q and R became thin. I deleted column R again. Same, still thin. I deleted column R one more time. Still thin.

Now, I checked the border again, using "borders/more borders" in the menu bar. This is what I found:

column Q had a right thin border,
column R had a left thin and right medium border,
column S had a left medium border,

At this moment, I deleted column R. Alas! There was no border between column Q and R. What happened? Beats me.

Scott Huish · Jul 6, 2011

What kind of borders do you want?

Code:

Sub test()
Columns("R:R").Insert
With Range("Q" & Rows.Count).End(xlUp).Resize(, 5)
    .HorizontalAlignment = xlCenter
    .Borders.Weight = xlMedium
End With
End Sub

yky · Jul 6, 2011

HOTPEPPER said:
You shouldn't need anywhere near that amount of code. Does this do what you want?

Code:

Sub test() Columns("R:R").Insert With Range("Q" & Rows.Count).End(xlUp).Resize(, 4) .HorizontalAlignment = xlCenter .Borders.Weight = xlMedium End With End Sub

Yes. You are correct. I don't need that much code.

The following is what works for me. Thank you for your help.
=================================
Columns("R:R").Insert
Columns("R:R").Insert
Columns("R:R").Insert
Columns("R:R").Insert

With Range([r6], [q65536].End(xlUp).Offset(0, 4))
.HorizontalAlignment = xlCenter
.Borders.Weight = xlThin
.BorderAround xlContinuous, xlMedium
End With

disappearing border

yky

Well-known Member

Excel Facts

Scott Huish

MrExcel MVP

AlphaFrog

MrExcel MVP

yky

Well-known Member

yky

Well-known Member

AlphaFrog

MrExcel MVP

yky

Well-known Member

Scott Huish

MrExcel MVP

yky

Well-known Member

Similar threads

Forum statistics

Share this page

disappearing border

Well-known Member

Excel Facts

MrExcel MVP

MrExcel MVP

Well-known Member

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock