If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Excel > How to unformat cells

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Jan 2002
Location: Bay Area
Posts: 507
Question How to unformat cells

I have an Excel file that is about 900,000 bytes in size which I am developing for a group of people to share. There are about a dozen tabs and macros to perform data entry in the worksheets, and some worksheets are used to run reports from data in the other worksheets. VBA and a form are used in this application. Cell bordering exists for all used cells, and is added by VBA when a report is run.

I want all the worksheets to appear unformatted in the areas outside of the cells that are used. This would be like a new worksheet where the cell borders are grey.

I tried removing border formatting by clearing the borders of all unused cells using Format Cells/Border to do this, and this increases the size of the workbook significantly. I also tried copying unformatted cells from another worksheet to all unused cells of a report worksheet, but that increases the file size by several megabytes also.

Question: how do I unformat unused cells that have black bordering, so that the bordering will appear grey, and doing this without adding to the file size.
Unused cells are deleted by the VBA code and this controls the file size, but I would like to control each worksheet's appearance by having all the unused cells appear without black borders. The black borders don't go away after the rows are deleted.

Jerry
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Oct 2003
Posts: 1,091
Howdy, Jerry. The "gray lines" you see in a new workbook are gridlines, not borders. So, if you want that option, then look at ToggleGrid as a way to control what the user sees.
__________________
old, slow, and confused
but at least I'm inconsistent!

Rich
(retired Excel 2003 user, 3/28/2008)

How to ask a question on forums
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Jan 2002
Location: Bay Area
Posts: 507
Thanks shades. I want to be able to control what the user sees and avoid saving unnecessary formatting in the Excel file's unused cells, which can add many megabytes to what started out relatively small.

The solution below seems to do the trick before running a report, where column headings in rows 1 through 3 are preserved. The results are that the gridlines in the columns below the report appear as they would in a new workbook (light grey), and the size of the file remains under 1 megabyte. For some unknown reason the unused cell gridlines would be black, and they would remain that way even after deleting all unused rows.

Rows("4:65536").Delete
ActiveWindow.GridlineColorIndex = 15

Jerry
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On