This is the scene. Imagine 2 lists of data, each list 2 columns wide (like 2 lists of names and phone numbers). List 1 covers column A and B, List 2 covers column D and E. I want it so that on the push of a button, columns A-E are automatically resized to be just big enough for their longest content, and column C resizes so that column D is on the next page.
The general idea is that you can print the sheet, and put together the pages relating to the seperate lists.
I am trying to create a userform that is reusable by turning on and off diff objects, and reusing objects. I am running into a little difficulty of resizing the list box for various lengths if items Example would be if I have a list of items and the longest one is 93 chars long, I need a width of 672 When I have a list that the items are each 5 chars long, I need a listbox width of 92
For simplicity's sake, I am using Courier (supposed to be a monospaced font) pitch is 10. I would have figured that my width would be simply a matter of finding the longest length in the list and then multiply that times some magical number that represents the width of the letter (since monospaced, each letter should be the same).
But, with the 93 char long string, the "average length" of each letter seems to be 7.2 (672/93 = ~7.2) but for the shorter words, it seems to be wider at 18 (92/5 = ~18)
Is there a way to have the listbox size itself to the width of the strings?
When I run the macro, some columns are already hidden. The macro doesn't seem to autosize cells correctly. For instance, one cell in a row appears to have some contents hidden (or below the reading area of the cell). In other instances, the rows are auto-size to huge heights and widths.
I'm struggling with getting my Excel worksheet to fit nicely on the pg for printing to .pdf. I could tinker with column widths manually to work this out for a printed report. But I need to print a couple hundred reports with varying column widths, so I need a vba solution.
The context is that I'm using vba to process two sets of interlinked data on a worksheet. I only want to print one set of data, and keep the second set out of the print view. To do this, I'm keeping the first set of data in columns 1 to 10 and the second set of data in columsn 21 to 30. I set the print range to only include columns 1 to 10.
This works fine for keeping the second set of data off the printed pg. The problem is that there is always a gap between the end of column 10 and the maximum width of the printed pg. Given the formatting of the report, this doesn't look very good.
I know that page setup offers the "fit to" option. This would work if I wanted to fit everything on the worksheet on the pg, but I don't. I just want to fit columns 1 to 10 to the pg. Is there a way to do this, allowing for varying widths in columns 1 to 10? Is there a way to do it via vba?
I needed to match the width a merged area of seven columns to a single column width (for row autofitting). Adding the column unit values and setting the single column to that value produced a significantly narrower width.
The documentation mentions that the column width unit is scaled to the font type and size and the absolute width is given in points.
This is set by the normal style setting in Excel Options or by VBA application.standardfontsize = 8 (in this case).
For instance, ten columns of Arial font 8 at 8.5 units you would think to be equivalent to a single column of 85 units. In points, the difference is 420 vs. 386.25, or 33.75 points.
Well, the standard character zero has a width at this setting of 4.5 points and 1 unit is 8.25 points, leaving 3.75 points for margins.
Then (10-1) margins allowances time 3.75 points resolves the difference.
Determining the margin allowances is straightforward, and reveals that the gradation with size is stepped by MS design. For instance, sizes up to 11 use 3.75 points for margins and increasing points for characters (except between 9 & 10). Sizes 12 through 18 use 5.25 points, 20 & 22 use 6.75, 24 & 26 use 8.25, etc.
I have created a table for this purpose, however I rarely use a "normal" other than 8, so I can probably use that set in programming.
I would like to know if it's possible to change width of different cells in a single column? Example:
Width of cell A1 would be 10, and width of cell A2 would be 15. Can it be done? If yes how, if no damn. The reason I ask is because I trying to fit a report on one page with a chart and a month worth of data, but some cells are to long and they get cut off.
Suppose I have the following UDF, which returns the width of the column, but when the column width change (increase or decrease), the UDF is not able to recalculate the column width...what should I do? .....
I've got a Listbox in a User form that looks at data on another spreadsheet purely for informational purposes, not to be selected. I have two problems with what it's doing:
1) There are 23 columns, of varying widths. However the listbox uses a standard width, so some items are truncated and some have too much room.
2) The data changes, and the number of rows may change from 2 to 15000. I've set the RowSource to cover the ranged from A1:W15000, but if only 2-300 lines are selected the user can't grab the scrollbar button to scroll through, but must instead use the arrow, which is rather slow.
Is there any way to set the column widths to match the data, and the Range to only go as far as the last populated row?
I need to know how to format columns in a spreadsheet. What I want to do is autofit the columns, all except for Column A; I want that want at a fixed width of 9. The code below is my attempt to do this, and it's not resulting as I wish. Stepping through the code, it performs the first part beautifully and autofits all of the columns. Where I have the line
For the past 4 months I have been desperate to find a way to split my sheet so that I can determine the column widths above and below that point independently.
For example:
small width from a1-b1 but really large width from a2-b2
Scenario: Two computers running Windows 7 (64-bit Professional) with Excel 2007, same processor, same hard drive, same memory, same everything except the monitors - see below
Computer 1: HP LA2006x monitor (20" viewable diagonal) @ 1600x900 resolution, 91.79 PPI Computer 2: HP LA2206x monitor (21.5" viewable diagonal) @ 1920x1080 resolution, 102.46 PPI
We have an Excel spreadsheet that the column widths on columns A through K must equal exactly 6, 1, 6, 1, 8, 1, 13, 2, 8, 1, and 35 respectively in order for the data to be imported into another program. These exact column widths are set on Computer 1 and the .xslx file is saved to a network location.
Computer 2 opens the .xslx file from the exact same network location. The column widths display as 5.86, 0.92, 5.86, 0.92, 12.84; not the exact columns listed above. So if anyone needs to export the data from Computer 2, they have to manually adjust the column widths or run a macro to auto adjust the column widths.
My question, is there anything I can do on Computer 1 or Computer 2 to make it so the column widths are saved and open correctly no matter which one is saving and which one is opening? The 20" monitor on computer 1 cannot display the 1920x1080 resolution... already tried that.
Looking for a way to print out a worksheet with auto number while printing. The worksheet does not have a header or a footer and contains needed information.
I have a working macro where I use a Forms Command Button w/ a Macro that copy/paste specific variables and range in the workbook to a pre-defined PowerPoint template file (in the same directory as the workbook file). See code below: Questions:
1) Is there a better script to reference a value in a cell value in Excel and Paste in PPT template?
2) Is there away to auto scale the copy range from Excel to PPt? Curently I have to manually adjust the height/width parameters.
I am trying to auto size cells depending on the lenght of the data in them. I have added some code in the work book on the attached file, this works on cell that i maunally add data to any where on the active page (template) but the cells that are auto updated with currencey data dont expand?
If you double click column H a list appears select a test and this auto populates the charges accociated with it
Is there a way to do this as it just fills with #### ???
The code in this workbook is below
[code] Private Sub Workbook_SheetChange(ByVal Sh As Object, _ ByVal Target As Range) Dim actCol, curCol
I have created a dynamic chart in excel where either the number of Series or the number of Data Points will change dependent on user inputs in the model. I've done this using the Offset Function in a Defined Name in Excel and dropped this Defined Name into vba code using ActiveChart.SetSourceData.
Anyway, all works fine, pulling the correct data, etc. What doesn't work is the final appearence of the chart. The chart has a Legend placed at the bottom of the chart, as standard, unless there is only one Series, when I remove the legend altogether. Whilst the legend works fine, the Plot Area of the chart doesn't update automatically to accomodate the changing size / existence of the Legend. Does anybody know how to do this? I tried using hieght, etc properties, but I don't know how to make them variable
I have a user form on which I need to create a textbox every time the user clicks a button. There are too many to create them all in advance and make them visible when needed. The sample attached, from this forum, is good, except, the numbers in the text box just scroll along and I need to see all the text typed into my textboxes. I would like the textboxes to be multiline, wordwrap and have them resize according to the amount of text.
What I am trying to do is to give the user the ability to insert a photo and have it autosize into a range of cells--so far I can only get it to work with a single cell and a predefined file. Can this be refined so that rng references a group of cells and pic somehow lets the user input a file name or pick its name and location?
Sub test() On Error Resume Next Set pic = ActiveSheet.Pictures.Insert("C: ange.gif") On Error Goto 0 If Not pic Is Nothing Then 'Found it!' Set rng = ActiveCell With pic .Height = rng.Height .Width = rng.Width .Left = rng.Left .Top = rng.Top End With End If End Sub
I am after a code that will sort out the below printing problem
I want excel to hide ALL OF column E from the printer, i want to see the information myself but when i press print, column E will not show on my printed page
i have found this Private Sub Workbook_BeforePrint(Cancel As Boolean) If ActiveSheet.Name = "Sheet1" Then Cancel = True Application.EnableEvents = False Application.ScreenUpdating = False With ActiveSheet .Range("e1").EntireColumn.Hidden = True .PrintOut .Range("e1").EntireColumn.Hidden = False End With Application.EnableEvents = True Application.ScreenUpdating = True End If End Sub
but am not sure how to enter it, also if the code is correct. I have entered it using the alt+f11 then clicked on this sheet, and put it in there but it still dont work
I have a spreadsheet with a graphic in the right side of a particular cell. I'd like to write code to reposition the graphic if the user changes the column width.
Is there an event I can use to put my code in? I see no "Column Resize" event.
Is it possible to have different size cells in a single column. For column A I would like to have a width of 30 up to row 20 and then after row 20 I would like a width of 15.
Can a single(or multiple) column be shown on sheet but not printed? I have a list with a bunch of vlookups in column C-H, a numbered column in A, the code used for the vlookup in B. I do not want to print the code(column B) but do want to print A,C-H. I do need to see the column B in sheet. How do I do this?
