applying gridlines only to used cell range

ndbhatt

New Member
Joined
Nov 18, 2005
Messages
14
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>
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
Hi there,

Can you describe a little more about "I want to apply gridlines ONLY to range of cells that are used in an worksheet."? Do you mean while viewing in your worksheet? If so, you would only be able to simulate it with borders, which it looks like you are trying to do. If you wanted the gridlines when you print, that option is in File | Page Setup | Sheet (tab) | Gridlines..
 

ndbhatt

New Member
Joined
Nov 18, 2005
Messages
14
details explanation

I am having a macro that creates reports for each region. Within region (representing each workbook) there would be sub-regions (representing each worksheet) where there are varying number of records in each worksheet.
My macro works fine for fetching results and other functionality. However, I need to show gridlines in each worksheet for records that are present.

e.g. if there are 9 records in sheet A then the gridlines would be there only for those records
and if there are 10 records in sheet B then the gridlines would be there only for those 10 records. so on so forth...
In nutshell, since I don't know how many records will be there in each of the worksheet, I need to use USED.RANGE to get the range and then apply gridlines (with one point thin line) formatting to it.
Before applying the gridlines, I am removing existing grids that appear by default in the excel view.
I hope i have tried to put my case clearly.
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
They don't appear in Excel by default. I think we're still confused here. Answer me this again please:

Are you wanting this for just viewing the worksheet, or for printint?
 

ndbhatt

New Member
Joined
Nov 18, 2005
Messages
14

ADVERTISEMENT

This is default view followed by expected view
Final Report_TO BE RUN ON LOCAL DRIVES AND NOT ON NETWORK DRIVES.xls
KLMN
26.66.350.30
38.357.75-0.6-0.6
48.78.700
57.658.1500
611.758.2-3.55-3.55
78.78.55-0.15-0.15
88.58.950.450
99.057.95-0.55-0.55
MainDataDump




I don't want to see those grey lines of grid. Instead, I want gridlines in black or whatever to be seen only for the data range displayed in that worksheet.



So finally it should look like
Final Report_TO BE RUN ON LOCAL DRIVES AND NOT ON NETWORK DRIVES.xls
KLMN
26.66.350.30
38.357.75-0.6-0.6
48.78.700
57.658.1500
611.758.2-3.55-3.55
78.78.55-0.15-0.15
88.58.950.450
MainDataDump
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
Maybe you could use something like this ...


<font face=Tahoma New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>

<SPAN style="color:#00007F">Sub</SPAN> ShowBorders()
    <SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet, Start <SPAN style="color:#00007F">As</SPAN> Worksheet
    <SPAN style="color:#00007F">Dim</SPAN> LastRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, LastCol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    <SPAN style="color:#00007F">If</SPAN> ActiveWorkbook <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> Start = ActiveSheet
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ActiveWorkbook.Worksheets
        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> IsWsBlank(ws.Name, ws.Parent.Name) <SPAN style="color:#00007F">Then</SPAN>
            LastRow = ws.Cells.Find("*", after:=ws.Cells(1, 1), _
                                    searchorder:=xlByRows, _
                                    searchdirection:=xlPrevious).Row
            LastCol = ws.Cells.Find("*", after:=ws.Cells(1, 1), _
                                    searchorder:=xlByColumns, _
                                    searchdirection:=xlPrevious).Column
            <SPAN style="color:#00007F">With</SPAN> ws.Range("A1", ws.Cells(LastRow, LastCol))
                .BorderAround xlContinuous, xlThin, xlColorIndexAutomatic
                .Borders(xlInsideVertical).Weight = xlThin
                .Borders(xlInsideHorizontal).Weight = xlThin
                ws.Activate
                ActiveWindow.DisplayGridlines = <SPAN style="color:#00007F">False</SPAN>
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> ws
    Start.Select
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Function</SPAN> IsWsBlank(wsName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, <SPAN style="color:#00007F">Optional</SPAN> wbName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> cnt <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    <SPAN style="color:#00007F">If</SPAN> ActiveWorkbook <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Function</SPAN>
    <SPAN style="color:#00007F">If</SPAN> wbName = "" <SPAN style="color:#00007F">Then</SPAN> wbName = ActiveWorkbook.Name
    cnt = Application.CountA(Workbooks(wbName).Sheets(wsName).Cells)
    <SPAN style="color:#00007F">If</SPAN> cnt = 0 <SPAN style="color:#00007F">Then</SPAN> IsWsBlank = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN></FONT>


HTH
 

ndbhatt

New Member
Joined
Nov 18, 2005
Messages
14
that worked like a charm

It not only worked like a charm but reduced my file size by half and so is my processing time.

Thanks a lot

no wonder this forum rocks!!! :p
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
Btw, this does not take into account if your data set shrinks in size, the gridlines will still be populated where they were last set. You can easily add some lines of code to first take away all gridlines right before you add them. Personally, I would write another routine and call at runtime, just for keeping the code modular and (at least for me) easier to read.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,755
Messages
5,597,929
Members
414,193
Latest member
bb60

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top