Last column sorting for fixed rows through VBA

**tabkaz** · 10-20-2021, 06:15 AM

trying to fix the code for sorting on the last column of every sheet the sorting on rows to be done at Rows 3:20 & 22:32.
Found the below code but I am unable to incorporate my desired rows in it. unable to crack how to define the rows in the below code.

image.jpg

Sub jusho()    
    Dim lColumn As Long
    lColumn = Cells(1, Columns.Count).End(xlToLeft).Column
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    
    Range(Cells(2, 1), Cells(LastRow, lColumn)).Sort key1:=Range(Cells(2, lColumn), Cells(LastRow, lColumn)), _
       order1:=xlAscending, Header:=xlNo
End Sub

**BadlySpelledBuoy** · 10-20-2021, 07:45 AM

Will the rows that need sorting always be the same across all sheets?
Will the last column be the same on each sheet?

BSB

**tabkaz** · 10-20-2021, 07:48 AM

Rows will be the same but the last column will change as per the data.

**BadlySpelledBuoy** · 10-20-2021, 08:05 AM

Without a sample workbook it's difficult to test but give this a try.

Sub SortMultipleSheets()
    Dim ws As Worksheet
    Dim lCol As Long
    
    For Each ws In ThisWorkbook.Sheets
        With ws
            lCol = .Cells(2, Columns.Count).End(xlToLeft).Column
            .Range("A3").Resize(18, lCol).Sort Key1:=.Cells(3, lCol)
            .Range("A23").Resize(10, lCol).Sort Key1:=.Cells(23, lCol)
        End With
    Next ws
End Sub

BSB

**tabkaz** · 10-21-2021, 01:42 AM

Thanks, it works,
it's working fine for sorting in ascending order, what to add if I have sorted in descending order for all the sheets?

**BadlySpelledBuoy** · 10-21-2021, 02:10 AM

Happy to help

BSB

**tabkaz** · 10-21-2021, 02:12 AM

it's working fine for sorting in ascending order, what to add if I have to sort in descending order for all the sheets?

**tabkaz** · 10-21-2021, 02:32 AM

have adjusted the code for sorting in descending order,

Sub SortMultipleSheets()
    Dim ws As Worksheet
    Dim lCol As Long
    
    For Each ws In ThisWorkbook.Sheets
        With ws
            lCol = .Cells(2, Columns.Count).End(xlToLeft).Column
            .Range("A3").Resize(18, lCol).Sort Key1:=.Cells(3, lCol), Order1:=xlDescending, Header:=xlNo, _
     MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
                    
            .Range("A23").Resize(10, lCol).Sort Key1:=.Cells(23, lCol), Order1:=xlDescending, Header:=xlNo, _
     MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
                      

        End With
    Next ws
End Sub

**BadlySpelledBuoy** · 10-21-2021, 02:27 AM

Try this:

Sub SortMultipleSheets()
    Dim ws As Worksheet
    Dim lCol As Long
    
    For Each ws In ThisWorkbook.Sheets
        With ws
            lCol = .Cells(2, Columns.Count).End(xlToLeft).Column
            .Range("A3").Resize(18, lCol).Sort Key1:=.Cells(3, lCol), Order1:=xlDescending
            .Range("A23").Resize(10, lCol).Sort Key1:=.Cells(23, lCol), Order1:=xlDescending
        End With
    Next ws
End Sub

BSB

Last column sorting for fixed rows through VBA

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Last column sorting for fixed rows through VBA

Re: Last column sorting for fixed rows through VBA

Re: Last column sorting for fixed rows through VBA

Re: Last column sorting for fixed rows through VBA

Re: Last column sorting for fixed rows through VBA

Re: Last column sorting for fixed rows through VBA

Re: Last column sorting for fixed rows through VBA

Re: Last column sorting for fixed rows through VBA

Re: Last column sorting for fixed rows through VBA

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Add rows in Alternate columns, divide by fixed rate per column.

RTD sorting a fixed amount of share prices- listed in a column- highest to lowest-dynamic

[SOLVED] Sorting columns with rows with a fixed "Sort" button

[SOLVED] Select Cells Individually, Fixed Columns, Fixed Rows, Every Other Row

Sorting 5 Columns keeping the earlier column data fixed

Sorting Rows by Value in a column

Help Converting Single Column(With a lot of rows) to Multiple(with Fixed row #)

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions