+ Reply to Thread
Results 1 to 4 of 4

excel can't clear formatting

  1. #1
    Registered User
    Join Date
    05-16-2005
    Posts
    33

    excel can't clear formatting

    I am using excel 2007 with 64bit windows 7

    I am unable to clear the formatting of rows or columns in a spreadsheet.
    If I change the default column width and/or row width then Ctrl-A to select the whole sheet then go to the Home tab and under Editing click Clear and select either Clear All, then all formatting is cleared except the column and row widths. If I use Clear Formats, nothing happens. Any ideas why not? I have tried this on both new and existing spreadsheets and get the same result for each.

    Is there any other way to reset the column and row widths?

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: excel can't clear formatting

    Column widths/row height are not formats, formats are generally cosmetic that excel doesnt really need, and are mainly to help people see things better. Color, font, bold etc are formats and formats are not data, excel works with data

    Having said that, you can highlight all rows and columns (click that small triangle above 1 and to the left of A), then right-click on 1 and enter 12.75 (thats my row height - you may be able to test yours on an unadjusted row), and then repeat for a column and enter 9 (or test 1 of yours)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    05-16-2005
    Posts
    33

    Re: excel can't clear formatting

    Thanks for your reply which makes sense but doesn't explain what I have happening. You say color, bold etc are formats and so will not be treated as data therefore not affected by Clear All.
    When I use Clear All, bold, merged cells, color, some rowheights, right, left and centre justification are all reset to default and all cells are set to blank. If I use Clear Formats, bold, merged cells, color, some rowheights, right, left and centre justification are all reset to default. Column widths are not set to default and [I]some/I] rowheights are set to default. Why some rows are reset and others are not is beyond me. In one worksheet I have set some rows to wrap text and centre text vertically and horizontally. When I use Clear Format on this sheet, all get reset to defaults. On another sheet in the same workbook, I have a row formatted in the same way and Clear Formats resets everything except the row height to default.

  4. #4
    Valued Forum Contributor john55's Avatar
    Join Date
    10-23-2010
    Location
    Europe
    MS-Off Ver
    Excel for Microsoft 365
    Posts
    2,028

    Re: excel can't clear formatting

    maybe it's not what you want but ....try it using thsi piece of code
    (add a new module, copy/paste and run the code)
    Please Login or Register  to view this content.
    Regards, John55
    If you have issues with Code I've provided, I appreciate your feedback.
    In the event Code provided resolves your issue, please mark your Thread as SOLVED.
    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

    ...enjoy -funny parrots-

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. clear number formatting
    By carmie in forum Excel General
    Replies: 4
    Last Post: 06-13-2012, 04:58 PM
  2. clear me the doubt in copnditional formatting
    By N.sangeetha in forum Excel General
    Replies: 3
    Last Post: 05-23-2012, 10:50 AM
  3. Clear Cell Value and Change Formatting.
    By newbie1234 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-15-2012, 09:21 PM
  4. Add cell formatting to Clear macro?
    By dillinger in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-26-2010, 09:31 PM
  5. [SOLVED] clear all except formulas, conditional formatting and validations
    By BitsofColour in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 12-20-2005, 10:30 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1