+ Reply to Thread
Results 1 to 12 of 12

Is there a way to remove all cell padding?

  1. #1
    Valued Forum Contributor
    Join Date
    02-26-2010
    Location
    Chattanooga, TN
    MS-Off Ver
    Excel 2003/2007/2010/2016
    Posts
    432

    Question Is there a way to remove all cell padding?

    Hello!

    I have this situation:

    Fixed B column width: 90
    A1= User enters text.
    B1= Row. Autofits using VBA.

    The formula in B1 is ="my"&A1.

    There is a certain length of text that the user can type where when the autofit works, it adds an additional hrt (char(10)) with no text on it. If the text length is slightly shorter, you don't get that hrt.

    I don't want to keep changing the column width to accomodate the longer text in A1. I just want it to do the word-wrap without Excel deciding "the text length is within my cell's padding so I need to add a hrt."

    Is there a way to specifiy that I want no padding for a range of cells, and if the cell is going to hit the wall then wrap the text?

    (I attached a file to illustrate what I am talking about.)

    I appreciate any help with this!

    VR/Lost
    Attached Files Attached Files

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Is there a way to remove all cell padding?

    You're not going to fix that -- it's an interaction between the screen driver and the printer driver. You might mitigate it by using the same font in the cell as as for Style Normal (you're using 11 pt in the cell, and Normal is 10 pt).
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Valued Forum Contributor
    Join Date
    02-26-2010
    Location
    Chattanooga, TN
    MS-Off Ver
    Excel 2003/2007/2010/2016
    Posts
    432

    Re: Is there a way to remove all cell padding?

    shg,

    I was thinking that might be the case.

    I found this Googling-around:

    http://sites.google.com/site/davesex...attredirects=0

    It looks they used offset to work around the cell's left pad. My VBA skills are horrible, but looking at it, can something like this be done for a cell's right pad?

    (BTW, when I click on 10 and normal, it works, but it won't let me make 11 the normal.
    ?)

    VR/Lost

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Is there a way to remove all cell padding?

    I don't see how that relates to your post, but it is totally gratuitous use of code for shading those cells.

    Select them all and get rid of the fill.

    Then select E2:AE2, and do

    Format, Conditional Formatting, Formula is =COLUMNS($E1:E1)<=$A2 and set a fill color you like.

    Copy down.

  5. #5
    Valued Forum Contributor
    Join Date
    02-26-2010
    Location
    Chattanooga, TN
    MS-Off Ver
    Excel 2003/2007/2010/2016
    Posts
    432

    Re: Is there a way to remove all cell padding?

    shg,

    What I meant was that in the Googled Excel file, the OP wanted to remove the padding for the cells so that the boxes went all the way to the left edge. The answer used some VBA code to make that happen.

    (Your response here showed that the VBA for that particular problem was overkill. I was just wondering if that VBA and use of offset could be used to remove right cell padding to help me with my file posted in this thread.

    That extra blank row that Excel throws in there is bad news for my project, so maybe there is a VBA fix. cell.padding = 0 or something like that.

    Thanks!

    VR/Lost

  6. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Is there a way to remove all cell padding?

    There is no 'padding' of cells, no 'cell margins' - the concepts don't exist in Excel.

    You can format the cells to not wrap text, and the row height will not increase. You can use a fixed-pitch font, like Courier or Consolas.

    Missing from all this is a description of what you're actually trying to do.
    Last edited by shg; 01-06-2011 at 07:06 PM.

  7. #7
    Valued Forum Contributor
    Join Date
    02-26-2010
    Location
    Chattanooga, TN
    MS-Off Ver
    Excel 2003/2007/2010/2016
    Posts
    432

    Re: Is there a way to remove all cell padding?

    shg,

    How it is used: Just like the cellpadding.xls I posted. User types down the B column (top left) and then I use a bunch of concatenates and IF in the C column (bottom right).

    An example of this would be those madlib stories:

    A1: Date B1:1/2/2011
    A2: First Name B2: Bob
    A3: State B3: Data Validation List of States

    So: the formula in C4 would be something like:

    ="On "&TEXT(B1,"dd mmm yyyy")&", "&B2"& " drove to "&B3&"."&char(10)&"On that day..."

    I have the PrintArea set to only C4.

    There is a macro button when pushed which: runs a spell checker on B1:B3, autofits the row height for row 4, brings up the print dialogue (the toolbar print is disabled as well as right-click), etc.

    This method works for any form letter because the user just types the variable stuff and never touches the actual letter. And you can set up error checking for each of those cells to lock down the user input to be almost error-free.

    It's that autofit that is messing up, because depending on what the user types for the Name in B2 (for instance), it could run the C4 text all the way to the edge of C4 and then do that goofy extra row thing.

    For some form letters, the formulas get so convoluted and lengthy that I frequently get the "formula too long" popup. So I have to break those into two cells (one over the other) (C4 and C5). I control the hrts with char(10)'s. The fact that Excel is putting in padding char(10)s where I don't want them is what I was asking for help on.

    Right now, I am surfing to see if there is a monotype (fixed-pitch) font that looks close enough to Times New Roman to fake it.

    HTH and thanks!

    VR/Lost

  8. #8
    Valued Forum Contributor
    Join Date
    02-26-2010
    Location
    Chattanooga, TN
    MS-Off Ver
    Excel 2003/2007/2010/2016
    Posts
    432

    Re: Is there a way to remove all cell padding?

    shg,

    Courier (not Courier New) does the same thing. When so many characters get entered, it shows that blank row.

    The problem is that if you ever-so-slightly widen the column to fix that trouble row, it could cause another cell further down to have the same problem as its text now goes against the right wall.

    So, I think any VBA which automatically widens the column if it finds the blank row might get caught in a loop fixing the column to fix one row, and then refixing the column to fix a new problem row, etc.

    Unless you have any more ideas, it looks like I am stuck with living with this undesired Excel behavior.

    Thanks for your help and working with this!

    VR/Lost

  9. #9
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Is there a way to remove all cell padding?

    If I get your thrust, you should be using Word's mailmerge, and use Excel as the mailmerge source. That's a combination purpose-designed for what you're trying to do.

  10. #10
    Registered User
    Join Date
    03-07-2019
    Location
    Michigan, USA
    MS-Off Ver
    2013
    Posts
    1

    Re: Is there a way to remove all cell padding?

    This is a very old post. Replying as this was the only post that described my exact problem.
    Playing with the cells I discovered if selected a cell and clicked in formula bar and hit Enter without doing anything the extra line added by auto-fit went away.
    I replicated this by VBA code and looped through my range re-entering the formula. It worked to remove all extra lines but for some rows one extra line remained.

    I found a better solution from another poster.
    For your range, increase column width to maximum size that you know you'll never have (I used 100).
    Then select all rows in the range and do an 'autofit'
    Then select all columns and do an 'autofit'

    You'll get cells perfectly auto-fitted with no padding.
    I used a simple macro to do this quickly.

  11. #11
    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,917

    Re: Is there a way to remove all cell padding?

    Thanks for the update
    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

  12. #12
    Registered User
    Join Date
    11-11-2019
    Location
    Phoenix, AZ
    MS-Off Ver
    Office 2016
    Posts
    1

    Re: Is there a way to remove all cell padding?

    I have to respectfully disagree with the statement about there being no "padding" in Excel. I have a column of text data with Word Wrap enabled. On the right margin, I would like to have the right-most character come as close to the cell border as possible before wrapping to another line. This happens when the the text wraps, but if the text is one line only, then approximately 31 pixels are inserted, and the cell height increases automatically to accommodate a second line, which isn't necessary. I've read numerous posts and other information about cell margins and padding, but none address this issue. To me, the padding is embedded somewhere in Excel or Windows...but it is padding nonetheless.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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