I have a 31,000 line spreadsheet, that will become a printed book. Based on the current column width (which has to stay the same for printing purposes) there are many lines that are only one line of text, and others that wrap to multiple lines. For some reason, for lines that are only one line (no wrapping needed) excel creates slightly larger padding below the text (even after auto fit), which creates a strange visual break (as if it's a new paragraph) and also makes the document longer than it needs to be because of the extra spacing. I'd like to get rid of that extra padding, but havn't found any solutions out there. I wondered if I could have a macro go through and adjust the column height to a specific height for any cells that contain fewer than a 90 characters (most of the one-line cells are under 100 characters, so I chose 90 to be safe). Any help would be enormously appreciated!
Recommended Answers
Jump to PostWhat specific height?
Jump to PostYou'll have to adjust the sheet name, the cell numbers (wS1.cells(x,x)) and the RowHeight but the following will get you where you need to be.
Sub ShrinkToFit() Dim wS1 As Worksheet Dim aRng As Range Dim oC As Range Set wS1 = Worksheets("Sheet1") Set aRng = wS1.Range(wS1.Cells(2, …
Jump to PostI see you've come back to Daniweb well after I posted my last comment. Can you please let me know if that worked and if so mark the thread as solved?
It really is a common courtessy to those (like me) that take their own time to help others (like …
Jump to PostIt's freezing because it's making a fair amount of changes to the layout of the sheet. I'm sorry, I should have turned off screen updating in the example I gave you.
Sub ShrinkToFit() Dim wS1 As Worksheet Dim aRng As Range Dim oC As Range Application.ScreenUpdating = …
Jump to PostI just tested this code on 150,000 lines and it took about 20 seconds. I'm not sure why it's taking so long with your workbook. Do you have a bunch of calculations going on on the sheet in question?
All 22 Replies
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.