• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Concise Code

Tripp

Member
Hello,

I have written a small macro to copy data from one sheet to another, nothing fancy.

When the data is pasted I have a small section to format some of the cells. Just borders and Fill colours.

It works, but the code doesn't look great and I was hoping I could get some guidance on how to make my code more concise.

Cheers,
Tripp

Code:
Header = "R2:AF2"

With wsTarget
    .Range("R2") = "Load(KN)"
    .Range("S2") = "Cover Depth (m)"
    .Range("T1:AF1").Merge
  
    .Range("T1") = "Pipe Diameter(mm)"
    .Range("T1").HorizontalAlignment = xlCenter
    .Range("T1").Interior.ColorIndex = 37
End With

With wsTarget.Range("T1:AF1").Borders
    .LineStyle = xlContinuous
    .Color = 0
    .Weight = xlThin
End With

Range("Internal_Diameter").Copy
wsTarget.Range("T2").PasteSpecial xlPasteValues, , , Transpose:=True

With wsTarget.Range(Header).Borders
        .LineStyle = xlContinuous
        .Color = 0
        .Weight = xlThin
End With

wsTarget.Range(Header).Interior.ColorIndex = 15
 
Last edited by a moderator:

Chihiro

Excel Ninja
Code generally looks fine. But without looking at sample workbook, can't really comment.

In general, you can't really condense formatting code. My recommendation is avoid use of format unless it's absolutely necessary.
 

Tripp

Member
Thanks Chihiro,

I wouldn't usually put formatting in but the recipient was intent on having it so I included it.

Good to know I'm not the only one who finds formatting code messy.

Thanks again.
Tripp
 

Marc L

Excel Ninja
Hi !​
The messy part is to merge cells as often it can be avoided via Center across selection …​
 
Top