sorting macro affects row height

[Randy Starkey]

Hi,

I have a macro that sorts, and it is somehow affecting my row heights that
have been set manually. Any code I can use to stop this?

Thanks!

--Randy Starkey

 

[Ryan.Chowdhury]

You probably have the cells formatted to wrap text. You can switch
that off but then your long entries will get cut off as they move to
smaller cells.

Ryan

 

[Randy Starkey]

Ryan,

Actually I use Alt-enter to get more text to display. But I think you're
right, the wrap is still probably set. Here's the issue - if I do some
alt-enters, and adjust row height manually I get a nice look. Then hit my
sort macro and boom! The row height goes to auto-fit. Any way in a macro to
get around that?

Thanks!

--Randy

 

[Dave Peterson]

I would think that autofitting the rowheight would be sufficient.

Can you select all the cells
then double click on one of the lines between the row numbers?

 

[Randy Starkey]

Dave,

Autofit breaks down after so many lines/characters of text - and I have a
lot - so I have to manually add some CR's and then set the height. But this
macro seems to strip my manual changes. Not on the CR's, but on the height.

--Randy

 

[Dave Peterson]

I've found that if I sprinkle alt-enters every 80-100 characters, then things
behave better.

How often do you add those alt-enters to your strings?

(I don't have any real good suggestion.)

 

[Randy Starkey]

Dave,

Yup. That's what I do too. And then manually set the height. But the macro
somehow trashes it. Maybe someone good in VBA will have an idea of some code
I could include to stop this.

Thanks!

--Randy

 

[Dave Peterson]

I see now. I had misremembered some of the problem.

How about this.

You add a column to the range that shows the rowheight for that row.

You sort the data (including that column) and then after the sort, you use the
value in that column to reset the rowheight. Then you clean up that helper
column.

This may give you a start.

Option Explicit
Sub testme()

Dim myRngToSort As Range
Dim wks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim FirstCol As Long
Dim LastCol As Long
Dim iRow As Long

Set wks = Worksheets("sheet1")

With wks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
FirstCol = 1
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

For iRow = FirstRow To LastRow
.Cells(iRow, LastCol + 1).Value = .Rows(iRow).RowHeight
Next iRow

Set myRngToSort = .Range(.Cells(FirstRow, LastRow), _
.Cells(LastRow, LastCol + 1))

With myRngToSort
.Sort key1:=.Columns(1), order1:=xlAscending, _
header:=xlYes
End With

For iRow = FirstRow To LastRow
.Rows(iRow).RowHeight = .Cells(iRow, LastCol + 1).Value
Next iRow

.Columns(LastCol + 1).ClearContents
End With

End Sub

Dave,

Yup. That's what I do too. And then manually set the height. But the macro
somehow trashes it. Maybe someone good in VBA will have an idea of some code
I could include to stop this.

Thanks!

--Randy

 

[Randy Starkey]

That sounds like it might be in the right direction... - if I only knew how
to code that I'll work on it over the weekend and see how close I can
get. Thanks!