sorting macro affects row height

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

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 wrote:
> 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,

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

<[email protected]> wrote in message
news:[email protected]...
> 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 wrote:
>> 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
>

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 wrote:
>
> 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
>
> <[email protected]> wrote in message
> news:[email protected]...
> > 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 wrote:
> >> 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
> >

--

Dave Peterson

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" <[email protected]> wrote in message
news:[email protected]...
>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 wrote:
>>
>> 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
>>
>> <[email protected]> wrote in message
>> news:[email protected]...
>> > 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 wrote:
>> >> 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
>> >
>
> --
>
> 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 wrote:
>
> 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" <[email protected]> wrote in message
> news:[email protected]...
> >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 wrote:
> >>
> >> 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
> >>
> >> <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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 wrote:
> >> >> 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
> >> >
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

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" <[email protected]> wrote in message
news:[email protected]...
> 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 wrote:
>>
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> >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 wrote:
>> >>
>> >> 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
>> >>
>> >> <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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 wrote:
>> >> >> 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
>> >> >
>> >
>> > --
>> >
>> > Dave Peterson
>
> --
>
> 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

Randy Starkey wrote:
>
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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 wrote:
> >>
> >> 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >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 wrote:
> >> >>
> >> >> 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
> >> >>
> >> >> <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> > 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 wrote:
> >> >> >> 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
> >> >> >
> >> >
> >> > --
> >> >
> >> > Dave Peterson
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

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!

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
>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
>
> Randy Starkey wrote:
>>
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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 wrote:
>> >>
>> >> 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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> >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 wrote:
>> >> >>
>> >> >> 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
>> >> >>
>> >> >> <[email protected]> wrote in message
>> >> >> news:[email protected]...
>> >> >> > 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 wrote:
>> >> >> >> 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
>> >> >> >
>> >> >
>> >> > --
>> >> >
>> >> > Dave Peterson
>> >
>> > --
>> >
>> > Dave Peterson
>
> --
>
> Dave Peterson