Results 1 to 3 of 3
  1. #1
    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

  2. #2
    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

  3. #3
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •