I previously asked a question that was answered by Variatus, but have noticed a minor problem when using the code.
See original post
https://www.teachexcel.com/talk/2479/sort-ascending-by-specific-column-some-sheets-excel-vba
Seems I still have a problem with this code.
Row 1 of some of those sheets is getting AutoRowHeight resized too.
I have row 1 set at 42, as I have some button shortcuts in A1 and some text for headings.
It is not the active sheet, as I have an ActiveX button on the Main sheet calling the macro to sort.
When I go through the sheets after sorting, there are random sheets that the first row has resized.
If the sheet has no data in rows 2 and below, it resizes the row height of row 1 of those sheets.
How to exlcude sorting row 1 or alternately, have row 1's height specified everytime.
Seems a waste to reset it every time, but better to exclude it.
Sub SortByKAscending()
' 25 Jan 2018
Dim Ws As Worksheet
Dim SortRange As Range
Dim Rl As Long
Dim i As Integer
Application.ScreenUpdating = False
For Each Ws In ActiveWorkbook.Worksheets
With Ws
Application.StatusBar = "Sorting sheet " & .Name
If .Name <> "Main" And .Name <> "Customers" And .Name <> "Not Identified" Then
' determine the last used row in sort column
Rl = .Cells(.Rows.Count, "K").End(xlUp).Row
Set SortRange = Range(.Cells(2, "A"), .Cells(Rl, "M"))
With SortRange
.Sort Key1:=.Cells(11), Order1:=xlAscending
.Cells.WrapText = False
.Columns(10).Cells.WrapText = True
.Rows.EntireRow.AutoFit
End With
i = i + 1
End If
End With
Next Ws
With Application
.ScreenUpdating = True
.StatusBar = ""
End With
MsgBox i & " worksheets were sorted.", _
vbInformation, "Sorting complete."
End Sub
How to solve this minor problem?
Thanks in advance.