Dynamically Autofit Row Height in VBA

[rasinc]

I am not sure if my approach is correct or not. I have copied some named ranges from one workbook to another. This seems to work ok.

The problem is that within the named ranges that I am copying, there are some merged cells and some cells with wrapped text. The row heights do not seem to copy with the code

wbPickTicket.Sheets(strSheetName).Range("A1").PasteSpecial Paste:=xlPasteAll

So, I am attempting to set the row height to autofit based on the largest height needed. I am using the code below.

For x = 1 To lngLastRow + 10
    Range("A" & x).EntireRow.AutoFit
Next x

The code appears to only look at column A to determine what the row height should be. However, in some cases the amount of text in column C may be the most so it should expose that text.

My next thought would be that I should loop through all possible columns and rows in my final sheet. I know how to do this but I am thinking that once it gets to Column C, it will expand the row height to actual and then in Column D it will bring it back to a standard Autofit of 15 again instead of something like 50 for Column C.

So I was wondering if there was an easier way?

An example of text in a cell like C1 would be

ABC Company Inc.
123 Anywhere St.
Somewhere, Missouri 12345
Tel: (555) 555-5555 Fax: (555) 555-5577

But A1, B1, D1 would have nothing, etc. until J1 which might have one line of text and at present only goes over to column L in the full block.

Each named ranged being copied in below each other should have the same number of columns, but the rows will need to be varying heights based on the maximum text in any cell in the row.

I hope I have explained this correctly. Any ideas are greatly appreciated. TIA, rasinc

 

[Wookiee]

Seems to me like all you need to do is change your column from "A" to "C" like in the example below. If the cells in C are going to take up the most space, I would think that Column C would be the only column you'd need to worry about auto-fitting?

For x = 1 To lngLastRow + 10
    Range("C" & x).EntireRow.AutoFit
Next x

This is just my hypothesis, mind you; when I tested the code in a dummy workbook, it worked using your original syntax with column A instead of C.

If that doesn't work, I would bet that the fact that some of your data is in merged cells is probably the culprit. I know such cells can cause VBA great consternation at times.

Hope that helps.

 

[rasinc]

Sorry, I should have been clearer. The example I gave is that in the one case, the highest rowheight is in Column C, but as I move down the different rows, sometimes it's in Column D, sometimes in Column A. I guess I assumed, since I was using the EntireRow command it would look at the entire row, figure out what height is necessary to show all cells in the row and then set it.

It works for me for any height of text in Column A. With the cells unmerged, it looks like I need to set the columns widths manually first (they didn't copy either), then allow the row heights to autofit.

I'm also a little surprised the RowHeight and ColumnWidth is not copied. So now I am back to the idea that I might have to look through all rows after I copy them and determine the heights and the widths as well, set them individually and move on. There has to be a better way. Any ideas are helpful.

 

[Wookiee]

I have a couple of suggestions. I hope one of them might help you out.

In the first scenario, I would suggest having your VBA code set the column widths for your destination worksheet. Then, after it's copied your named ranges into the destination sheet, run autofit code something like this:

Range("C2:C" & lngLastRow + 10).Rows.AutoFit

Alternately, if you want to have a uniform-looking spreadsheet, you could figure out what the max number of lines in a single cell would be, auto-fit that row, then hard-code your VBA so that all of your rows are the same height. Something like:

Rows("2:" & lngLastRow + 10).RowsHeight = 55

 

[rasinc]

Thanks Wookiee,

Your idea of using a range larger than a cell to select before I do the autofit seems to help. I just need to make sure I adjust the column widths first, then the rows will autofit properly now with

Range("A" & row & ":N" & row).EntireRow.AutoFit

I think it would be a good idea to keep their templates to specific columns widths anyway as they will be wanting to print one page wide. I can just sort out what widths they need to be and get them to set up the templates to the same specs.

Thanks for the help.

 

[Wookiee]

You're most welcome. I'm happy my advice was able to help.