(pretty easy) row height question

[Hoffman]

Hi all,
Thanks for any help.

I have like 60 rows that are all different heights because they have different amount of text and data in them.
I know how to make the row higher so I can see all the data and I even found the auto adjust the heights of the rows to all fit.
But after I sort my data, those row heights are messed up because they seem to be for the row # and when I sort that data and it has moved, I then am forced to readjust the height on some each time.

Is there a way that when I set a row height it stays that height for that row of data even if it moves from sorting?

Hope this makes sense,

Thanks again for any advice.

Barry

 

[shinigamilight]

Just type whatever you want in the cell and then click wrap text then adjust the height. Now even if you will sort, the row height will be maintained.

 

[frabulator]

Or you could run a macro after the autosort to autofit. I created this after recording an 'AutoFit' example. I modified it a little to make it easier to select the starting and ending row

Sub Macro1()
    Dim StartRow As Integer
    Dim EndRow As Integer
   
    StartRow = 1
    EndRow = 35
    Rows(StartRow & ":" & EndRow).EntireRow.AutoFit
End Sub

 

[Hoffman]

Just type whatever you want in the cell and then click wrap text then adjust the height. Now even if you will sort, the row height will be maintained.

Thanks for trying to help.
I do have wrap text on, I know that for sure.
Then I adjust the height and all is good.
It is after the sorting that is the issue as when I just the height, I think it is adjusting that ROW #'s height and so when I sort, that data goes to a different row with a diff and wrong height.

make sense?
Not sure what I am doing wrong.

Barry

 

[Hoffman]

Or you could run a macro after the autosort to autofit. I created this after recording an 'AutoFit' example. I modified it a little to make it easier to select the starting and ending row

Sub Macro1()
    Dim StartRow As Integer
    Dim EndRow As Integer
  
    StartRow = 1
    EndRow = 35
    Rows(StartRow & ":" & EndRow).EntireRow.AutoFit
End Sub

That's is cool and I will try,

maybe I just ned to hit select all cells, row, auto fit after each sort?

I just thought it would always auto fit the row height I guess even after I sorted.

let me try your Marco (I have never added a macro before)
Thanks
Barry

 

[frabulator]

maybe I just ned to hit select all cells, row, auto fit after each sort?

I just thought it would always auto fit the row height I guess even after I sorted.

I think that is a totally separate property. Because of this, unless you combine both of the functions together into one subroutine (like a Macro in my example), then you will have a two step process. Or, at least, that is my understanding. Someone may very well come along and say I am wrong and give a better solution.

let me try your Marco (I have never added a macro before)

To get started with Macros, the best way is to record yourself doing some things.

Include the developer tab first (File> Options> Customize Ribbon> check the 'Developer' tab located on the right list box) and click OK.

From there, go to the developer tab and select 'Record Macro'. Do something in Excel, and then click 'Stop Recording'.

After you recorded your macro, click on 'Visual Basic' and navigate to (on left) VBAProject ([your book name]) > Modules> and your recorded macro (probably Module 1). From here you can see the steps you took to record your macro.

By having your courser in the macro you can also run the macro by clicking the play button up near the top under the menu option for 'Debug'.

All of this is outline here on Microsofts page.

This will lead you down into the rabbit hole of VBA, so be warned that it can get complicated. But, this is a great tool to see how Excel handles your inputs and what codes equate to what action.

---------------------

In the case of your question, what I would do is have three subroutines.
1: Sorting
2: AutoFit
3: Run both so I only have to call one.

Please see the attached codes:

Sub RunBoth()
    SortingStuff
    AutoFit
End Sub


Sub AutoFit()
    Dim StartRow As Integer
    Dim EndRow As Integer
  
    StartRow = 1
    EndRow = 35
    ActiveSheet.Rows(StartRow & ":" & EndRow).EntireRow.AutoFit
End Sub
 
Sub SortingStuff()

    Columns("A:P").Select
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add2 Key:=Range("A1:A35") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range("A1:E35")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub

I would then link these to a button that I can click so both will run when needed.

Here are two websites that explain how to do it. You dont have to do EVERY step, because it gets into adding these buttons to a custom ribbon and yahda-yahda, but its there in case you do. I, personally, would just add a button to the workbook in question and link the 'RunBoth' sub to the button. Quick and easy.

(the above code is just a starting point. Please adapt it to your needs)

 

[Hoffman]

I think that is a totally separate property. Because of this, unless you combine both of the functions together into one subroutine (like a Macro in my example), then you will have a two step process. Or, at least, that is my understanding. Someone may very well come along and say I am wrong and give a better solution.


To get started with Macros, the best way is to record yourself doing some things.

Include the developer tab first (File> Options> Customize Ribbon> check the 'Developer' tab located on the right list box) and click OK.

From there, go to the developer tab and select 'Record Macro'. Do something in Excel, and then click 'Stop Recording'.

After you recorded your macro, click on 'Visual Basic' and navigate to (on left) VBAProject ([your book name]) > Modules> and your recorded macro (probably Module 1). From here you can see the steps you took to record your macro.

By having your courser in the macro you can also run the macro by clicking the play button up near the top under the menu option for 'Debug'.

All of this is outline here on Microsofts page.

This will lead you down into the rabbit hole of VBA, so be warned that it can get complicated. But, this is a great tool to see how Excel handles your inputs and what codes equate to what action.

---------------------

In the case of your question, what I would do is have three subroutines.
1: Sorting
2: AutoFit
3: Run both so I only have to call one.

Please see the attached codes:

Sub RunBoth()
    SortingStuff
    AutoFit
End Sub


Sub AutoFit()
    Dim StartRow As Integer
    Dim EndRow As Integer
 
    StartRow = 1
    EndRow = 35
    ActiveSheet.Rows(StartRow & ":" & EndRow).EntireRow.AutoFit
End Sub
 
Sub SortingStuff()

    Columns("A:P").Select
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add2 Key:=Range("A1:A35") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range("A1:E35")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub

I would then link these to a button that I can click so both will run when needed.

Here are two websites that explain how to do it. You dont have to do EVERY step, because it gets into adding these buttons to a custom ribbon and yahda-yahda, but its there in case you do. I, personally, would just add a button to the workbook in question and link the 'RunBoth' sub to the button. Quick and easy.

(the above code is just a starting point. Please adapt it to your needs)

Wow- thanks for the major lesson. That seems like a lot but I will attack it.
Appreciate your time and knowledge on this.

Barry