I want to apply gridlines ONLY to range of cells that are used in an worksheet. I tried to use usedrange in the following code but it applies gridlines to all the cells below that range too. Number of columns A to W
<font face=Courier New> <SPAN style="color:#007F00">'Formatting of the report starts here</SPAN>
Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ActiveWorkbook.Worksheets
ws.Activate
<SPAN style="color:#00007F">With</SPAN> ActiveSheet
<SPAN style="color:#007F00">'===</SPAN>
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
<SPAN style="color:#007F00">' This is in loop for all worksheets having</SPAN>
<SPAN style="color:#007F00">' different number of rows however, the column remain the same.</SPAN>
.UsedRange.Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
.UsedRange.Select
<SPAN style="color:#00007F">With</SPAN> Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">With</SPAN> Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">With</SPAN> Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">With</SPAN> Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">With</SPAN> Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">With</SPAN> Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#007F00">'===</SPAN>
.Range("A1:W1").Select
Selection.AutoFilter
<SPAN style="color:#007F00">'ActiveSheet.UsedRange.Select</SPAN>
Selection.AutoFormat Format:=xlRangeAutoFormatList1, Number:=True, Font:= _
<SPAN style="color:#00007F">True</SPAN>, Alignment:=True, Border:=<SPAN style="color:#00007F">True</SPAN>, Pattern:=True, Width:= _
True
.Cells.Select
.Cells.EntireColumn.AutoFit
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">Next</SPAN> ws
<SPAN style="color:#007F00">'Formatting of the report ends here</SPAN></FONT>
<font face=Courier New> <SPAN style="color:#007F00">'Formatting of the report starts here</SPAN>
Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ActiveWorkbook.Worksheets
ws.Activate
<SPAN style="color:#00007F">With</SPAN> ActiveSheet
<SPAN style="color:#007F00">'===</SPAN>
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
<SPAN style="color:#007F00">' This is in loop for all worksheets having</SPAN>
<SPAN style="color:#007F00">' different number of rows however, the column remain the same.</SPAN>
.UsedRange.Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
.UsedRange.Select
<SPAN style="color:#00007F">With</SPAN> Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">With</SPAN> Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">With</SPAN> Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">With</SPAN> Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">With</SPAN> Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">With</SPAN> Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#007F00">'===</SPAN>
.Range("A1:W1").Select
Selection.AutoFilter
<SPAN style="color:#007F00">'ActiveSheet.UsedRange.Select</SPAN>
Selection.AutoFormat Format:=xlRangeAutoFormatList1, Number:=True, Font:= _
<SPAN style="color:#00007F">True</SPAN>, Alignment:=True, Border:=<SPAN style="color:#00007F">True</SPAN>, Pattern:=True, Width:= _
True
.Cells.Select
.Cells.EntireColumn.AutoFit
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">Next</SPAN> ws
<SPAN style="color:#007F00">'Formatting of the report ends here</SPAN></FONT>