How to highlight a row in a listview?

Macro vba row 409 height limit question

• QUESTION: I¡¯m tasked with developing a solution for the 409pt Row Height limit in Excel 2007. On a spreadsheet of some 80 Rows and 10 Columns about four Cells in two of the Columns contain text that exceeds the 409 limits and neither show nor print in their entirety. I figure if I can create a Macro that locates the Rows over 409, inserts a new Row below it, changes the Height for both rows to 409, and merges and highlights the Cells required it would solve the problem. I managed to get most of it to work. I created the Macro by recording my actions using Row 3 as the 409 high Row. Run this way I get my desired results but only for Row 3, any other Rows are ignored despite the ¡°loop¡±. If any Row other than 3 is 409 high I get mixed results, the worst being an infinite number of Row adds because ¡°loop¡± reads the last added ¡°new¡± Row as an ¡°existing¡± Row. I believe problem points are adding only one new Row under a found existing Row wherever it is found and getting the appropriate Cells to merge each time. I expect there are wildcards to be used, but I don¡¯t know them. Can you help? I¡¯ve attached my work below. Sub Final_Test() ' ' Final_Test Macro ' Locate 409, Insert Row, Change Height, Merge Cells, Highlight ' ' Keyboard Shortcut: Ctrl+k ' Dim lng As Long lng = 1 Do While Not IsEmpty(Range("A" & lng).Value) If Rows(lng).RowHeight = 409 Then Rows("4:4").Select Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove ActiveWindow.ScrollRow = 2 ActiveWindow.ScrollRow = 3 ActiveWindow.ScrollRow = 4 ActiveWindow.SmallScroll Down:=-1 Rows("3:4").Select Selection.RowHeight = 409 Range("D3:D4").Select With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBot .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("K3:K4").Select With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBot .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 49407 .TintAndShade = 0 .PatternTintAndShade = 0 End With Range("D3:D4").Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 49407 .TintAndShade = 0 .PatternTintAndShade = 0 End With End If lng = lng + 1 Range("A3").Select Loop End Sub ANSWER: Kirk, this worked for me: Sub Final_Test() Dim lng as Long, lastrow as Long ' ' Final_Test Macro ' Locate 409, Insert Row, Change Height, Merge Cells, Highlight ' ' Keyboard Shortcut: Ctrl+k ' Dim lng As Long lastrow = Cells(Rows.Count, "A").End(xlUp).Row For lng = lastrow To 1 Step -1 If Rows(lng).RowHeight = 409 Then Rows(lng + 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Rows(lng).Resize(2).Select Selection.RowHeight = 409 Cells(lng, "D").Resize(2, 1).Select With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBot .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 49407 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(lng, "K").Resize(2, 1).Select With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBot .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 49407 .TintAndShade = 0 .PatternTintAndShade = 0 End With End If Next End Sub ---------- FOLLOW-UP ---------- QUESTION: Excellent, thanks Tom. With a couple of tweaks everything but the Merge worked, which leads me to two more questions if I may. On my test spreadsheet everything worked beautifully, but on the actual sheet the cells would not merge. That particular sheet in the workbook won¡¯t let me merge cells no matter what I try ¨C the check box remains greyed out. The sheet data is linked to another spreadsheet but even after unlinking that sheet I still couldn¡¯t merge. I was able to link the Sheet One info to Sheet Two of the same workbook and then merge, but I was hoping not to do that (doubles the work). Any ideas? The second question relates to the Row heights. The macro inserts the new Row and changes both to a specific height. Can it be made to fit the text so there isn¡¯t a huge space at the bottom of a 430 point Cell while an 800 point Cell fits snug? My research leads me to think ¡®no¡¯, but I figured I¡¯d ask.

• Answer:

  Kirk, If the sheet is unprotected, then I can't think of anything that would just restrice merging of cells. When strange things begin to happen, it can be an indicator that the workbook file is becoming corrupted and you want to make sure you have a good back up. Or begin to think about breaking in a new workbook and moving your data there. I can't say for sure. Of course there are hundreds of arcane bugs and strange interactions amongstfeatures. I try to be knowledgeable about how to usewhen it works but I don't claim to be a base of knowledge of all excel's frailties. I have run across workbooks that have a worksheet that behaves strangely. Usually it is easier to get rid of it or start a new workbook than to exhaust a lot of time trying to figure out the cause. As I said, it could be caused by some internal corruption rather than a "feature" ofand that type of thing isn't going to be known. As far as fitting the height of the cells to match the text, there is no built in support for it (unless you are a windows API and Font guru and then you would be working in the area of windows and not excel). Whatoffers is the Autofit rows and autofit columns capability. The problem is that it does not work with merged cells; at least not in xl2003 down to xl2007 when merged cells were introduced. I suspect this has not changed in Excel 2007 but really haven't had the need to test it. ( I just went and did a test and it appear autofit continues not to work with merged cells). A long time ago, someone asked how to do this in thenews group. Jim Rech and I independently came up with a solution of unmerging the cell and having the information in a single cell. Autofitting that cell and recording the height, then remergine and setting the height (all done in vba code). Jim's solution was a bit cleaner than mine. But I haven't seen any other solution since that time. for you, since your text doesn't fit within the maximum height of the cell - this solution is not feasible. Sorry.