Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Follow up on a question: Sorting with format

0

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.

Answer
Discuss

Answers

0
Selected Answer

Please replace the procedure you have with the one below.

Sub SortByK_Ascending()
    ' 31 Jan 2018
    Dim Ws As Worksheet
    Dim SortRange As Range
    Dim Rf As Long                              ' first row
    Dim Rl As Long                              ' last row
    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" Then
                Rf = 2
                ' determine the last used row in sort column
                Rl = .Cells(.Rows.Count, "K").End(xlUp).Row
                If Rl > Rf Then
                    Set SortRange = Range(.Cells(Rf, "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 If
        End With
    Next Ws
    With Application
        .ScreenUpdating = True
        .StatusBar = ""
    End With
    MsgBox i & " worksheets were sorted.", _
           vbInformation, "Sorting complete."
End Sub

The new code introduces the variable Rf (=first Row) and skips the sorting and formatting of any sheet where Rl (last Row) isn't larger than Rf. If Rl=Rf there would be only one entry on that sheet and no sorting required. Since resetting of the row height occurs in the same breath as sorting your problem should disappear.

I did declare a special variable for Rf because it seems that your sheets have different first rows. It might be necessary to determine the correct Rf for each sheet individually. If so the variable will be needed. For the moment, having it just improves the readability of the code a little. No harm done.

Discuss

Discussion

Thanks. That has solved the problem perfectly.
I have another problem, but will ask another question.
I can add a copy of the workbook which has been cleaned of customer data as well as a source file and a link to the original help received at Excelkey.
The working document has been very customised to my needs. Saves me more than an hour daily to do what it does for me.
Shaun1 (rep: 4) Feb 1, '18 at 1:12 am
And there they go complaining that national productivity doesn't increase lol: If but everyone would do as well as you do!
Variatus (rep: 4889) Feb 1, '18 at 1:20 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login