2005.02.21 11:45 PM

Autofit Excel Columns using Cells

A reader named Priya asked the following question on my earlier post regarding Excel's #'ing of Text-formatted cells:

We have currency format for a cell in the excel sheet. We have been trying to set autoFitWidth for a cell but no such feature is available. We cant set the width in the xsl as it will be reflected for all the cells in that column. We just want to increase the width in case the characters exceed the cell boundaries. Is there any such provision.

If I understand correctly, Priya is asking is how one can set the width of a column so that it properly displays the contents of one or more of the column's formatted cells without using the Autofit method on the entire column, as this will result in the column being expanded to accommodate the column's widest content, which may include text cells that don't need to be considered, because they will overflow properly anyway without showing #'s? Or, in fewer words, how does one Autofit columns using the contents of one or more (but not all) of the column's cells as the measure of the column widths desired?

I hope I got that right.

To illustrate, here's a sheet containing some mixed values in the first column:

Here's how the first column might appear if made too narrow:

Unfortunately, if the entire first column is Autofit, it will look like this:

What we really want is for the first column to be sized to fit the formatted values appearing in rows 2-4, like this:

Turns out this is easy to do. The problem is with Excel's documentation, which is sort of confusing. It describes the Range object's Autofit method like this:

Changes the width of the columns in the range or the height of the rows in the range to achieve the best fit.

[Range] must be a row or a range of rows, or a column or a range of columns. Otherwise, this method generates an error.

I think this explanation leaves folks believing that the Autofit method can only be executed on Range objects representing whole rows or columns, like this:

ActiveWorkbook.Worksheets("Sheet1").Columns(1).Autofit 

Of course, this results in columns being sized to their widest contents, as shown above, which is not what we want.

Actually, what the documentation should say is that the Autofit method can be executed on two kinds of Range objects (note that we're ignoring row-based Range objects, but the same explanation applies):

  1. Range objects that represent the entirety of one or more columns by address.

  2. Range objects returned by the Columns property of an Application, Worksheet, or Range object.

The first one is what we often think of as a column in Excel. This is basically a Range object having an address with no row qualifier. A Range object of this type can be retrieved directly using a column-only address, or from the indexed collection of full-column Range objects exposed by the Columns property of Application, Worksheet, and Range objects (as was done above), or from the EntireColumn property of a Range object. The following code illustrates each of these approaches:

?ActiveWorkbook.Worksheets("Sheet1").Range("A:A").Address
$A:$A
?ActiveWorkbook.Worksheets("Sheet1").Range("A:A").Cells.Count
 65536 

?ActiveWorkbook.Worksheets("Sheet1").Columns(1).Address
$A:$A
?ActiveWorkbook.Worksheets("Sheet1").Columns(1).Cells.Count
 65536 

?ActiveWorkbook.Worksheets("Sheet1").Range("A1").EntireColumn.Address
$A:$A
?ActiveWorkbook.Worksheets("Sheet1").Range("A1").EntireColumn.Cells.Count
 65536 

The second type of Range object supporting the Autofit method is not as common. It turns out the Columns property of the Application, Worksheet, and Range objects is not just a collection (as illustrated above). It's also a Range object. And a strange one at that.

Let's look at the Range object returned by the Columns property of another Range object. On the surface, the Range object returned by the Columns property appears to be the same as the Range object used to access the Columns property in the first place. For instance, they report the same address and number of cells:

?ActiveWorkbook.Worksheets("Sheet1").Range("A2:A4").Address
$A$2:$A$4
?ActiveWorkbook.Worksheets("Sheet1").Range("A2:A4").Cells.Count
 3 

?ActiveWorkbook.Worksheets("Sheet1").Range("A2:A4").Columns.Address
$A$2:$A$4
?ActiveWorkbook.Worksheets("Sheet1").Range("A2:A4").Columns.Cells.Count
 3 

However, there's an important difference. Many properties and methods of the Range object know the difference between a simple Range object and a Range object derived from another Range object's Columns (or Rows) property, and they adjust their behavior accordingly. For instance, the Range object's Count property normally returns the number of cells. But, for a Range object returned by the Columns property, the Range object's Count property returns the number of columns:

?Activeworkbook.Worksheets("Sheet1").Range("A2:A4").Count
 3 

?Activeworkbook.Worksheets("Sheet1").Range("A2:A4").Columns.Count
 1 

A more important difference, though, at least for the purposes of this post, is that when the Autofit method is executed on a Range object returned by another Range object's Columns property, the Autofit method is smart enough to only consider the contents of those cells belonging to the original Range object (from which the Columns property Range object was retrieved) when determining the desired column widths.

So, to answer Priya's question, you can Autofit the columns containing an explicit set of cells using the cells' contents as the basis for the desired widths by calling Autofit on the Range object returned by the Columns property of the Range object explicitly defined by the cells. Using our example sheet above, we can Autofit the first column based on the contents of the cells in rows 2-4 using the following code:

ActiveWorkbook.Worksheets("Sheet1").Range("A2:A4").Columns.AutoFit

By the way, this is what Excel's Format | Column | AutoFit Selection menu option does, except that it uses the Range object defined by the current Selection object.


Comments

I want to set width of a column in excel using xsl. can any body help me.

raju | 2005.06.03 04:06 AM

Raju,

I'm afraid I'll need a little more info. Can you explain in detail what it is you wish to do and how Excel and XSL fit in?

ewbi.develops | 2005.06.03 06:37 AM

I get a "type 13 mismatch " error when i am using Autofit method in one cell.
Worksheets(WorksheetDataProjects).Columns("A").AutoFit
Is there any specific type of cells in column "A" that I have to use for the AutoFit method.
The format of cells is General number and is containing text. maybe I should change to txt but this was not a problem before.

Thanks
Arba
A

Arba | 2005.06.12 10:17 AM

Arba,

Not sure, but I think the problem may be with the variable you're passing to the Worksheets collection: WorksheetDataProjects. What does it contain? If it's a string containing the name of the sheet you're targeting, or an Integer/Long with the sheet's index, then you're okay. However, I'm just guessing that the variable already holds a reference to the sheet you want, in which case the code would be:

WorksheetDataProjects.Columns("A").AutoFit

That's just the quickest way I know for you to have gotten a type mismatch error with your statement. If that's not it, let me know and we'll look further.

Good luck!

ewbi.develops | 2005.06.12 07:44 PM

Just what I needed - thanks! ;-)

mega | 2005.06.21 12:37 PM

You're welcome!

ewbi.develops | 2005.06.21 09:07 PM

I cannot seem to get the autofit funtion for a particular cell (but neither for an entire row) to print the entire contents of a textbox that I am using to accept input. The contents move over and when I select the particular cell that they are moved into, the entire contents are there, but they truncate. I have tried to pay attention to the fact that the maximum cell heighth is 405 but my cell will only autofit to 255.
The consequence of this is that my analysts write only to fit what will print. It works fine for three paragraphs or so, but when it is necessary to fit more content into the textbox, they don't do it because they know it will truncate when they go to print their worksheet.
This only affects printing. What am I missing?

Dan Hostetler | 2006.03.05 07:24 AM

Hi Dan,

Can you explain how the textbox contents are being moved into the target cell (programmatically, or via a VBA Forms textbox bound to a particular cell, etc.)?

How is the target cell formatted (number format, alignment, wrapping, etc.)?

When you say it only affects printing, do you mean that the target cell's contents appear in full on-screen but are truncated when printed? Are they truncated from the bottom only? If so, does the bottom correspond at all with the page bottom? Also, what print settings are you using (scaling, margins, center on page, etc.)? Finally, is the source textbox included in the print-out (assuming you are using a VBA Forms textbox on the target cell's sheet)?

ewbi.develops | 2006.03.06 12:38 PM

The textbox is inside of a userform that uses this method to move the contents over to a virgin worksheet:

Application.Worksheets("Notes to Solution Group").Cells(23, 5) = TextBox3.Text
Cells(23, 5).Rows.AutoFit

But this is my second autofit because I open up the sub() with another autofit:

Worksheets("Notes to Solution Group").Rows("1:41").AutoFit

The target cells are formatted arial 10 normal font, general, left and top, wrap, no background or borders, unlocked and unhidden. I would like to lock the sheet but it still leaves the target cells within the sheet unlocked and it doesn’t make any difference either way.

The cell characters truncate on screen and on paper. When I activate by click the cell, I can see everything in the formula bar, so I know it’s all there. It truncates from the bottom. I find that they seem to truncate at around fifteen hundred characters, more or less. I have tried to put in a few page breaks but the pages break quite normally and this seems to add an additional distortion to the problem.

My page setup is 100%, no scaling, centered with .25 margin on all sides. I am printing in black and white but there is no color on the page anyway (habit).

I am not printing out the textbox. This is just plain old cell stuff. It goes from a textbox into a regular cell. I can’t get the whole page to resize to the contents when I use textboxes on the printout. Probably ignorance, but those things resize up and down like they want it seems and my printouts are unpredictable.

I tried putting in a counter and adding a row below when it hit fifteen hundred characters so it would LOOK the same on the printed page but that messed up my vba because all the cells would have to change for the migration from textbox (with the additional row) and it got too complicated for me to figure out. Plus I can’t get the len(range(“A1”).value) function to produce a realtime character counter for the textbox that I am typing in. It misfires or just sits there (just like me). So I think this was too complicated of a solution anyway.

Thanks a million for any help you can give me. It is causing the analysts not to use my program and I our notes are helter skelter!

Dan Hostetler | 2006.03.08 04:00 AM

Hi Dan,

Thanks for the details. Now that I understand better what's up, I think it's fair to say that you're running into an inherent limit of Excel. As described here:

http://office.microsoft.com/en-gb/assistance/HP051992911033.aspx

"Length of cell contents (text) 32,767 characters. Only 1,024 display in a cell; all 32,767 display in the formula bar."

I've found the cell display (and print) limit is never exactly 1,024, but varies up or down a little depending on the font and font size selected for a cell. However, the cell's Value and Text properties never exceed these limits.

To avoid the complication of dynamically moving target cells, which you encountered when trying to measure the textbox length and inserting new cells/rows during the movement from userform to sheet, you might try simply moving all of the textbox values to your target cells as-is in one pass (no address changes), then come back and check the Len() of each potentially too-large cell's Value property, splitting their contents and inserting new rows then, if necessary.

Regarding your question (in your email) about showing a running character count on a user form, I figured the best way to explain this would be an example. Open a copy of this workbook:

http://www.ewbi.com/ewbi.develop/samples/textbox.measure.xls

In its VBA project you'll find a single UserForm with a single multi-line textbox that has a maximum characters setting of 1024. As you type in the textbox, it updates two labels showing the number of characters the textbox contains and the number of characters remaining.

Let me know if you have any additional questions. Good luck!

ewbi.develops | 2006.03.08 11:56 AM

Dear Folks,

I want to thank you for your help regarding the character length restriction on cells. I would have never guessed THAT one!

Also, the counter works like a charm. Our lives have improved.

However, I have a new one. I am trying to save one sheet in a workbook to whatever destination the user wants. I want to make sure overwrites are by choice and this is done. However, I have an annoying “Book1” open that I want to kill and cannot do. I want it gone, closed with save = false, and I want the user to return to the open program which contains the original sheet.

I admit to giving up a few too many brain cells in the late sixties but this one has me looping like a moron.

An added complication is that once the first “book1” is saved and if I want to save another copy (it happens), I am now dealing with book2 and book3 and so on.

Here is the code.

Private Sub CommandButton1_Click()

Dim mySaveName As Variant
Dim blnOKToSave As Boolean
Dim strName As String

Sheets("Notes to Solution Group").Select

mySaveName = "c:\desktop\" & Range("C51").Value

Do While Not blnOKToSave

mySaveName = Application.GetSaveAsFilename( _
InitialFileName:=mySaveName, _
FileFilter:="Excel Files (*.xls), *.xls")

If mySaveName = False Then Exit Sub

If Dir(mySaveName) <> "" Then
'it does - check user wants to overwrite
If MsgBox(mySaveName & vbCrLf & vbCrLf _
& "already exists. Overwrite?", _
vbYesNo) = vbYes _
Then blnOKToSave = True
Else
blnOKToSave = True
End If

Loop

'strName = Sheet20.Range("a44")
'ActiveWorkbook.SaveCopyAs Filename:=strName
ThisWorkbook.ActiveSheet.Copy
ActiveWorkbook.SaveCopyAs Filename:=mySaveName
BreakLinks

End Sub

Sub BreakLinks()
Dim WS As Worksheet, Rng1 As Range, Cell As Range
With ActiveSheet
On Error Resume Next
Set Rng1 = Cells.SpecialCells(xlCellTypeFormulas, 23)
On Error GoTo 0
If Not Rng1 Is Nothing Then
For Each Cell In Rng1
If Left(Cell.Formula, 2) = "='" Then
Cell.Value = Cell.Value
End If
Next
End If
Set Rng1 = Nothing
End With
End Sub

Thanks!
Daniel Hostetler

Dan Hostetler | 2006.03.11 04:09 AM

Hi Dan,

Traveling today, will look at what you've asked when I return.

ewbi.develops | 2006.03.13 11:55 AM

Hi Dan,

I want to recommend you replace this line:

ActiveWorkbook.SaveCopyAs Filename:=mySaveName

With these lines:

ActiveWorkbook.SaveAs Filename:=mySaveName, AddToMru:=False
ActiveWorkbook.Close SaveChanges:=False
ThisWorkbook.Activate

However, your call to BreakLinks after your call to SaveCopyAs is confusing me. BreakLinks appears to be trying to replace the contents of every formula containing cell on the just copied worksheet with its cell value instead. I can understand why you might want to do this (and there are other, perhaps better, ways to do it), but what I can't understand is why you are doing this *after* you save the copy? Assuming you mean for it to happen before you save the copy, then my suggestion becomes:

BreakLinks
ActiveWorkbook.SaveAs Filename:=mySaveName, AddToMru:=False
ActiveWorkbook.Close SaveChanges:=False
ThisWorkbook.Activate

Hope this helps. Good luck.

ewbi.develops | 2006.03.15 06:16 PM

I owe you dinner whenever you are in the Chicagoland area!

Thanks very very much . . .

Dan Hostetler | 2006.03.16 04:12 AM

I have a question still. Sorry.

The Breaklinks code you offered is identical to the saveasmy name code. Isn't that redundant and does it actually break the links? That's my first question.

The second question is how I define the MRU argument as the sub initializes.

That's it. Two easy cleanup questions I hope.

Take care - Daniel

Dan Hostetler | 2006.03.16 04:55 AM

...and if I take out the Most Recently Used code I get a runtime error '438' and it still seems to open up book1, book2, book3 and so on.

My stupidity on this seemingly small issue is driving me nuts!

Dan Hostetler | 2006.03.16 07:27 AM

Hi Dan,

I'm going to hold you to that dinner! La Chicanita in Arlington Heights has been a favorite of mine for years.

The suggested code above was not meant to replace BreakLinks. The three line suggestion was meant to replace this one line in your click handler routine:

-----------------
ActiveWorkbook.SaveCopyAs Filename:=mySaveName
-----------------

The later four line suggestion, which is exactly like the three line suggestion, expect with an added call to BreakLinks, was meant to replace these two lines of code in your click handler routine:

-----------------
ActiveWorkbook.SaveCopyAs Filename:=mySaveName
BreakLinks
-----------------

The reason I made two different suggestions was because I was unsure why in your original click handler code you were calling BreakLinks *after* you saved the worksheet copy. Based on my reading of the code, you should have been calling BreakLinks *before* the save. If my reading of the code is correct, then you should adopt the four line suggestion I made, which looks like this:

-----------------
BreakLinks
ActiveWorkbook.SaveAs Filename:=mySaveName, AddToMru:=False
ActiveWorkbook.Close SaveChanges:=False
ThisWorkbook.Activate
-----------------

And use this code to replace these two lines in your click handler routine:

-----------------
ActiveWorkbook.SaveCopyAs Filename:=mySaveName
BreakLinks
-----------------

Hope that's clearer. Let me know if not. Good luck!

ewbi.develops | 2006.03.16 08:54 AM

That did it! Thanks Arba!

Dan Hostetler | 2006.03.16 02:46 PM

Glad to hear it, Dan. (Btw, who's "Arba"?)

ewbi.develops | 2006.03.16 03:59 PM

Isn't that you? I have tried to figure out your name and just, kind of, borrowed another salution. Sorry. What is your name.

And there is one other thing, if it can be done, that I would appreciate some advice on and I promise I won't lengthen this blog anymore for awhile if you can help me once again.

The quesiton is this: Is there anyway to strip the macro module from the saved form so it is blank in the Visual Basic editor after it is saved? I'm not having any luck figuring that one out either. I have saved a form with a bunch of useless code in the back that I would rather not have there.

Plus, even though I have broken the links, there are formulas in the cells that I would rather not have either. Is there some way to save the contents of the cells?

Thanks and if I have imposed too much, just ignore me. I go away easily . . .

Daniel
www.sbp-solutions.com

Dan Hostetler | 2006.03.17 04:20 AM

And since you haven't triggered my "going away for awhile" I have another, last one to throw on the pile.

It's about this famous saved document that I'm almost done with. For some reason, even though I have a thousand characters in the original worksheet cell and they all appear in print preview and they print out in entirety, they do not all transfer on the save. I end up with about 255 characters (one fourth) of the cell data in the saved document.

It kind of defeats saving the document if only a small part of the data transfers over. It is not an autofit problem. The data is just not there in the saved document. It's truncated during the save.

Any ideas?

Dan Hostetler | 2006.03.17 05:28 AM

well I have solved the truncation because all I had to do was autofit the rows in the new document, however, when I move the data over I have to call over the contents of the mirror cell in the original document. Problem is that I then have to replace the forumua with the value of the cell, otherwise it keeps trying to get the data from the original document.

How do I simply replace the formula in the saved document with the transposed values?

Dan Hostetler | 2006.03.17 07:21 AM

Well, Dan, I admire your persistence. The name is Eric. And, unfortunately, I think we're beginning to exceed my ability to conceptualize what it is that you're doing. I'm not sure what workbook it is that you are copying formulas into or which one has a "macro module" that needs stripping "from the saved form". We're also straying a bit too far from the topic of this particular blog post, which will make it hard for others to follow. So, rather than continue here, why don't you send me an email at the address shown at the bottom of the left margin text and let's see if we can arrange to provide you some more direct and efficient assistance. I look forward to hearing from you.

ewbi.develops | 2006.03.17 09:30 AM

Hi I would like to ask the same question as Raju asked. I am using XSL to create the excel data and write it on to the stream. I have set Response.ContentType = "application/vnd.ms-excel"; and then the data created after applying the XSLT is written. I want to sent the width info also. Please help. Thanks in advance.

Vimal | 2006.05.10 11:00 PM

Hi Vimal,

I'm afraid my response is pretty much the same as I gave Raju, which is that I'll need some more info about what it is you're doing before I can help. For instance, how are you "using XSL to create the excel data"? What data? From where are you doing this? In what format is the "data" you're creating for Excel? If, as I think I can deduce from your question, you are trying to assemble and return something in ASP.NET for use by Excel on the client, what is it you are producing? In what format? And what are you producing it from in the first place? Are the widths you're referring to the widths of columns in Excel when it opens whatever it is you're producing and returning?

And, lastly, what does any of this have to do with the point of this post, which was about using Excel's Autofit method?

Get me some clues and I'll try to help. Promise.

ewbi.develops | 2006.05.11 05:37 PM

Sir,

Thank you for spreading the knowledge. I have reached to your page from Google. I am actually looking for some help in setting the width of column (i.e.) for example i would like to set the width of Column C in excel sheet to 6 as we do it with a mounse.

Awaiting You positive reply.

Thanking You

Nitin P Shah
nitinpshah@rediffmail.com

Nitin P Shah | 2006.11.13 02:06 AM

Hi Nitin,

Try the ColumnWidth property:

ActiveWorkbook.Worksheets("Sheet1").Columns(3).ColumnWidth = 6

It even works on range objects involving more than one column:

ActiveSheet.Range("E1:I20").ColumnWidth = 20

Good luck.

ewbi.develops | 2006.11.13 07:27 AM

Hi.

I'd like to know if there is a function (Excel/VBA) which returns the number of lines in an individual Excel cell.

see what i am trying to do is copy information from several worksheets and combine it into one list. The list is comprised of x number of rows by one entry merged across several columns for formatting purposes.

my problem comes when a particular item in the list gets too long and exceeds the width of the merged cells. I've set it to word wrap, however the height of the row does not "Best-Fit" to reveal the content.

I've researched the web, and it is possible though tedious to "Best-Fit" wrapped cells using macros. If possible, i would not want to run a macro to "best-fit" every individual entry as this only occurs to about 2% of my entries. My first attempt at solving this was finding out the number of characters in that entry that would make it 'wrap'. I'd then set a condition that if the number of characters in my entry exceeds the limit, then i would adjust the row height of that individual entry setting the row width accordingly based on the range the number falls in. Problem with this however is that this 'limit' varies differently depending on caps, space and even the different letters. So it's back to my original question of whether excel or vba has a function which returns the number of lines so i can set the row height based on how many lines that particular cell contains.

Would appreciate if you could help, or suggest a better solution. Thanks.

EricJ | 2007.01.15 03:41 PM

Hi EricJ,

As you've discovered, Excel won't AutoFit the height of a row having merged cells (whether across or down) that have wrapping on. Even if you explicitly ask it to via the menu or VBA. And, unfortunately, there's no easy way to determine how many lines the contents of a cell wrap to (merged or not). It's affected by so many things (column widths, font, font size, zoom level, etc.), I'm not even sure there's a hard way to do it (there are some text measuring Windows APIs, but factoring in the zoom, column widths, etc. sounds impossible).

Best bet would seem to be elimination of the merged cells so you can take advantage of Excel's wrapping and AutoFit. Or perhaps first pasting the copied content into a single wrapping cell (somewhere else on the sheet, or on another sheet) whose width is sized per the merged cells, AutoFit the row, and then take its height and apply it to the actual target row (containing the merged cells). However, I'm afraid I don't understand what you're trying to do well enough to know whether this is possible.

If you come up with a working approach, please come back and share it with us. Good luck.

ewbi.develops | 2007.01.15 08:57 PM

It is easy to autofit merged rows using the system the previous post starts to describe. All you do is copy the value of merged cells to a single cell IN THE SAME ROW that is the same width as the merged cell. Then, you simply autofit the row using code. That's all you need to do. You can easily hide your tracks too. If anyone is interested anymore, I'll provide further guidance.

BW | 2007.06.15 06:49 AM

Hi BW,

Thanks for the comment and offer. That's kind of what I was getting at, but didn't take the time to describe in enough detail I think. Maybe I'll write a post on this someday illustrating the technique. Certainly using the same row, as you've suggested, has some advantages, but I figured that if someone was already automating the copying of data from one cell to another (and the subsequent clean-up) then they could as easily move the data to another location in the worksheet/workbook, autosize, and retrieve the resulting height to apply to the merged rows. But either would work. Thanks again.

ewbi.develops | 2007.06.19 06:15 PM

Are there conditions that would prevent the AutoFit method from actually resizing columns? The following code is supposed to change the column width, but doesn't. The worksheet is unprotected, and it doesn't seem to matter if ScreenUpdating is true or false.

With ActiveWorksheet.Columns(1)
MsgBox .Width
.AutoFit
MsgBox .Width
End With

I get the same value for both MsgBox's, the column doesn't resize, and the cell's value is larger than the column width. Thanks.

Mike | 2007.10.10 09:50 AM

Nevermind, I just figured it out. The cells contain formulas and the macro temporarily disables calculation for increased speed. The AutoFit was being called before calculation is restored.

Mike | 2007.10.10 10:10 AM

Good catch, Mike. I think I've been bit by that scenario before myself. Thanks for sharing it.

ewbi.develops | 2007.10.10 01:34 PM

Hi,

When I autofit a worksheet on my PC it looks fine but when I email it to my boss he says the column widths are not autofitted and the spreadsheet looks terrible. Is there a way to fix this? Thanks.

Stephen | 2008.02.20 06:00 AM

Hi Stephen,

I've seen this, too. I've found that it is usually caused by the cells being formatted with the Normal style, which unless overridden inherits its font characteristics from the "Standard font" setting found on the General tab of the Tools Options window. What will happen is that the columns are sized per the widths of their contents based on the font and font size on one machine, then subsequently opened on a machine with a different "Standard font" setting and suddenly everything is wrong. The quickest solution is to explicitly specify the font family and size for the cells, either with a custom style or by direct application to the cells, then size them per their contents.

Hope that helps. Good luck!

ewbi.develops | 2008.02.20 01:31 PM

Excellent

jevin | 2008.09.25 02:51 AM

Hi,

I am trying to transpose data from a Sheet 1, columns , to Sheet,rows. However I do not want to the data from the columns in sheet 1 to be shown in one row. I want each column field to be shown in rows as a cluster of records. Meaning column 1(A:1) in sheet 1 should go to Sheet 2 in Row 1(A:1) and then have column 2 in sheet 1(B:1) to show in Sheet 2 Row 2(A:2) and so on.

Thabo | 2009.09.02 11:58 PM

Thabo,

I might be misunderstanding your question, but it seems like you could simply highlight the Sheet1 contents you want, Ctrl-C (or Edit | Copy) to copy them, and then from within cell A1 on Sheet2, use Edit | Paste Special... and on the subsequent options window check the Transpose option.

If the original data on Sheet1 was:

R1C1 R1C2 R1C3
R2C1 R2C2 R2C3
R3C1 R3C2 R3C3

Then after being copied and pasted with a Transpose on Sheet1, it will be:

R1C1 R2C1 R3C1
R1C2 R2C2 R3C2
R1C3 R2C3 R3C3

Column1 from Sheet1 now occupies Row1 on Sheet2, Column2 occupies Row2, etc.

Good luck!

ewbi.develops | 2009.09.03 09:18 AM

You are not far from what I want but the format of the data on sheet 1 should be as you have described it here:
R1C1 R1C2 R1C3
R2C1 R2C2 R2C3
R3C1 R3C2 R3C3

However I want the data on sheet 2 to display data from sheet 1 in the following manner:
R1C1
R1C2
R1C3

R2C1
R2C2
R2C3

R3C1
R3C2
R3C3

As you can see the data in 1 row needs to be spilt per column into different rows. I can load the workbook on a FTP site for you to have a look at if that can help.

Thabo | 2009.09.14 08:47 AM

So you want to flatten a set of multi-column, multi-row data into one column by traversing the source data in column-row order. I think I understand, but I don't believe this is possible without some coding. So are you looking for a non-coding solution? Or are you looking for help with the coding, whether in VBA or something else?

ewbi.develops | 2009.09.14 09:20 AM

Now we are on the right track; i have tried using Vlookups on the first three rows and tried highlighting and dragging these rows down but the next record that shows from R2C1 is not the second record in sheet 1 but record number four. I do believe that coding is the only solution but i am trying to figure out a code that can count rows.

Thabo | 2009.09.14 01:16 PM

I have come across a wierd problem. I have the following code:

On Error GoTo ResetEvents
Worksheets("Scratch").Unprotect
'NewTarget.Copy
With Worksheets("Scratch").Range("A1")
.Font.Name = NewTarget.Font.Name
.Font.FontStyle = NewTarget.Font.FontStyle
.Font.Size = NewTarget.Font.Size
.Interior.Color = NewTarget.Interior.Color
.WrapText = False
.Locked = False
.FormulaHidden = False
.Value = SourceTarget
End With
Worksheets("Scratch").Range("A1").Columns.AutoFit
' Calculate the number of characters per Point-width
LL = Worksheets("Scratch").Range("A1").ColumnWidth
NN = Len(Worksheets("Scratch").Range("A1"))
nCharsPerColumnUnitWidth = NN / LL
ResetEvents:
Application.EnableEvents = True


THis seems to work in a simple test spreadsheet with minimal VBA. However when I move it to my real workbook (using Excel 2007 on a .xls file) I find that, even stepping through the macro, as soon as it tries to execute the AutoFit commmand, it disappears off into never-never land, not even stopping at the GoTo Error address. How can I trap whatever error is causing this to happen?
Any other ideas? The sheet "Scratch" is clean (no code), empty, and not protected. There are no merged cells in "Scratch". Any ideas?

Ian | 2013.03.03 09:16 PM

BTW - the AutoFit happens in "Scratch" but it never stels to the next line LL = Worksheets....

Ian | 2013.03.03 09:28 PM

Hi Ian,

Sure hard to say with so little context. One thing I wondered about it whether there's an Option Explicit somewhere in the module and, if so, whether LL and NN are dim'd somewhere in that routine or in the module or globally? Where's this code being executed from (module, class, event, etc.), and what's triggering it? I see Application.EnableEvents is being set to true in the error handler, but where was it turned off? And what else was turned off - DisplayAlerts, etc.?

ewbi.develops | 2013.03.04 09:15 AM

Thanks for your rapid response. The code is being called by Worksheet_Change(ByVal Target As Range) from sheet "ENTRY" in which text is being entered into a merged field (unavoidable in this form). By I extract the text to pass on to the routine above via:
Call AdjustRowHeight(TargetText, Sheet12.Range(ARArowAddr), 15)

Sub AdjustRowHeight(ByVal SourceTarget As String, ByVal NewTarget As Range, StdRowHeight As Integer)
Dim TextLen, FieldLength, LL, NN As Integer
Dim Scratch As Range
Dim nCharsPerColumnUnitWidth
Dim ASheet As Object
On Error GoTo ResetEvents
... continue with previous code.
The problem I'm trying to solve is that the cell in NewTaget is linked to the merged field in "ENTRY SHEET" sheet by the formula
=T('ENTRY SHEET'!D52)
I extract the text from 'ENTRY SHEET'!D52 into the variable SourceTarget in the Call statement to avoid having to deal with merged cells.
The problem I am having is that the text in 'ENTRY SHEET'!D52 can occupy anything from 1 to three wrows of text in NewTarget. The fields in NewTarget are set to "Wrap", but they do not expand when I change the text length in "ENTRY SHEET". So I am trying to calculate how many lines would be needed by calculating nCharsPerColumnUnitWidth, and using that and the column width in NewTarget to adjust NewTarget's RowHeight.
Does that make sense. Else, is there anotherway to make the Wrap property in NewTarget trigger RowHeight value?

Ian | 2013.03.04 02:40 PM

Oh! Ignore the Dims for Scratch and ASheet - left over from testing dozens of ways around this. I originally had AutoFit inside the With ...End With, but then it did not even make it to the End With. Could it have something to do with the fact that I am working in a 2003 .xls file using Excel 2007?

Ian | 2013.03.04 02:50 PM

Well, this is only making partial sense to me, but that has more to do with my preoccupied mind than with your explanation. I don't think Excel 2003/7 is an issue. And using a scratch area to force an AutoFit then retrieve the resulting dimensions (or in this case letting it stretch full and trying to calculate the number of characters per column unit width) to apply explicitly to another range somewhere is a legitimate approach. And, as you pointed out, the Scratch sheet AutoFit apparently works, but then fails during execution of the next line, or at least bails out.

Not sure what's up and a quick set of tests doing similar things isn't causing me any problems. The only thing I can suggest is to try and reproduce this in a much smaller sample workbook, one having just two sheets (one source, one scratch), set the formatting (merge, etc.) on the source sheet to match your problem 'Entry Sheet'!D52, add the sheet event handler and AdjustRowHeight call and see if the problem goes away. If it does, then there are other issues in the larger workbook. If it doesn't go away, then you've got something much simpler to work with and you can send it over to me at the email address in the left column above.

Btw, I still don't see where EnableEvents is being turned off?

ewbi.develops | 2013.03.04 03:18 PM

Once again, thanks for your thoughtful response. I actually built this part of my application in a 3-sheet workbook (ENTRY SHEET, NewTarget sheet, Scratch sheet) and it worked perfectly. Thinking about it, the only difference was that the ENTRY SHEET cells were not merged. It worked perfectly. So I simply copied the macros from one to the other, adjusted variable names where necessary, and lo and behold - it bombed!
I am about to resort to a kludge was of doing this (hard-coding the nCharsPerColumnUnitWidth variable from physical measurement with a number of different strings and using the averag, and then continuing. That would avoid the AutoFit command altogether and I can move on from there. Unsatisfactory, but I am totally stumped as to how to make it work for multiple fonts and sizes set up in the NextTarget sheet.
FYI, I disable events in a number of places in my application, and that line is just there to make certain that if they were disabled from some other place, and not re-enabled, that would not be a problem.
I don't think I want to spend any more of your time (or mine for that matter) trying to track this down. Thanks again for your help. If any other readers could suggest what condition might cause AutoFit to abort the VBA program it's running in, I'd be happy to hear about it.

Ian Nixon | 2013.03.05 08:53 PM



Post a Comment

 
  (optional)
  (no html)
 
   


TrackBack

TrackBack URL:  http://www.typepad.com/services/trackback/6a00d8341c7bd453ef00d83447d4d953ef

Listed below are links to weblogs that reference Autofit Excel Columns using Cells: