ShumsFaruk
Board Regular
- Joined
- Jul 24, 2009
- Messages
- 93
Good Day All,
By subject it sounds familiar, but everyone requires VBA as per their own needs. I found many threads to find the last column but I would like to replace "H" Column to LastCol in below code:
Above code works fine to format my sheet.
Please help.
By subject it sounds familiar, but everyone requires VBA as per their own needs. I found many threads to find the last column but I would like to replace "H" Column to LastCol in below code:
Code:
Sub Format_UtilizationControl()
Dim Ws1 As Worksheet
Dim LR1 As Long
Set Ws1 = Worksheets("UtilizationControl")
LR1 = Ws1.Range("A" & Rows.Count).End(xlUp).Row
Ws1.Columns("A:H").Copy
Ws1.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
With Ws1.Columns("C:H")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Ws1.Columns("A:B")
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Ws1.Range("C1:G1").Merge
With Ws1.Range("C1:G1")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Ws1.Range("A1").ClearContents
Ws1.Range("A1:A2").Merge
With Ws1.Range("A1:A2")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Ws1.Range("B1:B2").Merge
With Ws1.Range("B1:B2")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Ws1.Range("H2").FormulaR1C1 = "Total"
Ws1.Range("H1:H2").Merge
With Ws1.Range("H1:H2")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Ws1.Range("A1:H2").Font.Bold = True
Ws1.Range("A1:H2").Borders(xlDiagonalDown).LineStyle = xlNone
Ws1.Range("A1:H2").Borders(xlDiagonalUp).LineStyle = xlNone
With Ws1.Range("A1:H2").Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 1
.TintAndShade = 0
.Weight = xlThin
End With
With Ws1.Range("A1:H2").Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 1
.TintAndShade = 0
.Weight = xlThin
End With
With Ws1.Range("A1:H2").Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 1
.TintAndShade = 0
.Weight = xlThin
End With
With Ws1.Range("A1:H2").Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 1
.TintAndShade = 0
.Weight = xlThin
End With
With Ws1.Range("A1:H2").Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 1
.TintAndShade = 0
.Weight = xlThin
End With
With Ws1.Range("A1:H2").Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 1
.TintAndShade = 0
.Weight = xlThin
End With
With Ws1.Range("A1:H2").Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight2
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With
With Ws1.Range("A1:H2").Font
.ThemeColor = xlThemeColorAccent6
.TintAndShade = -0.249977111117893
End With
Ws1.Range("A3").Select
ActiveWindow.FreezePanes = True
Ws1.Range("A3:H" & LR1).Borders(xlDiagonalDown).LineStyle = xlNone
Ws1.Range("A3:H" & LR1).Borders(xlDiagonalUp).LineStyle = xlNone
With Ws1.Range("A3:H" & LR1).Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 1
.TintAndShade = 0
.Weight = xlThin
End With
With Ws1.Range("A3:H" & LR1).Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 1
.TintAndShade = 0
.Weight = xlThin
End With
With Ws1.Range("A3:H" & LR1).Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 1
.TintAndShade = 0
.Weight = xlThin
End With
With Ws1.Range("A3:H" & LR1).Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 1
.TintAndShade = 0
.Weight = xlThin
End With
With Ws1.Range("A3:H" & LR1).Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 1
.TintAndShade = 0
.Weight = xlThin
End With
With Ws1.Range("A3:H" & LR1).Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 1
.TintAndShade = 0
.Weight = xlThin
End With
Ws1.Range("A" & LR1 & ":B" & LR1).Merge
With Ws1.Range("A" & LR1 & ":B" & LR1)
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Ws1.Range("A" & LR1 & ":H" & LR1).Font.Bold = True
With Ws1.Range("H3:H" & LR1).Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight2
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With
Ws1.Range("H3:H" & LR1).Font.Bold = True
With Ws1.Range("H3:H" & LR1).Font
.ThemeColor = xlThemeColorAccent6
.TintAndShade = -0.249977111117893
End With
With Ws1.Range("A" & LR1 & ":H" & LR1).Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight2
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With
With Ws1.Range("A" & LR1 & ":H" & LR1).Font
.ThemeColor = xlThemeColorAccent6
.TintAndShade = -0.249977111117893
End With
Ws1.Columns("A:H").EntireColumn.AutoFit
Ws1.Range("A2").Select
End Sub
Above code works fine to format my sheet.
Please help.