CODEWITHSUNDEEP
excelvbasorting
Danny M
Danny M

Reputation: 17

VBA sorting cell color with varying ranges and sheets

I am currently learning/messing around with VBA coding so I could write some macros for work. I could really use some help please.

Question: I want the code below (which was written by macros recorder) to add some specific codes for my needs.

Sub Sort()

    Range("A1:D23").Select
    ActiveWindow.SmallScroll Down:=-15
    ActiveWorkbook.Worksheets("Sheet8").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet8").Sort.SortFields.Add(Range("A2:A23"), _
        xlSortOnCellColor, xlDescending, , xlSortNormal).SortOnValue.Color = RGB(255, _
        255, 0)
    With ActiveWorkbook.Worksheets("Sheet8").Sort
        .SetRange Range("A1:D23")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply

End Sub

  1. The first line I would like it not to be static with specific range. Would substituting Range("A1:D23").Select for Range("A1").CurrentRegion.Select work for the varying ranges in my data tables?

  2. For lines 3, 4 and 5 where it says ActiveWorkbook.Worksheets("Sheet8"), how can I make it so that its active not only for sheet8 but for any sheet I open or create?

  3. Finally on line 4 Sort.SortFields.Add(Range("A2:A23"), I would like to change to the varying row amounts under that column from varying data.

Upvotes: 1

Views: 7149

Answers (1)

Paulo Avelar
Paulo Avelar

Reputation: 2140

This might help:

  1. Get how many rows your sheet has and work with it. See below for more information. Beware: it may be glitchy if the sheet is empty.

  2. Replace ActiveWorkbook.Sheets("Sheet 8") with ActiveSheet. Unfortunately, ActiveSheet does not provide autocomplete, but it is a Sheet object. Casting it to any Sheet object would give you that behavior.

  3. Well, once you want your code to deal with the same range twice, you might as well save it in a variable.

Considering your table may expand in rows and columns, you need to count them using the End command.

Here's the updated code:

Sub Sort()
    Dim sht As Worksheet
    Dim rngSort As Range
    Dim rngTable As Range
    Set sht = ActiveSheet        

    rowCount = sht.Range("A1").End(xlDown).Row
    Set rngSort = sht.Range("A1:A" & rowCount)
    Set rngTable = sht.Range(sht.Cells(1, 1), sht.Cells(rowCount, 1).End(xlToRight))

    sht.Sort.SortFields.Clear
    sht.Sort.SortFields.Add(rngSort, _
        xlSortOnCellColor, xlDescending, , _
        xlSortNormal).SortOnValue.Color = RGB(255, 255, 0)

    With sht.Sort
        .SetRange rngTable
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Take a look at my range. I use sht.Range because when you don't say where your range is, Excel assumes it is your selected sheet. If it isn't you must explicitly say so. In your case, it doesn't matter.

Upvotes: 2

Related Questions