Setting a SetRange to the highlighted selection

[gress032]

Hi Team,

Forgive me for some of my lack of knowledge of syntax and proper definitions on even the beginner details of VBA, but I have a certain scenario I need help with.

Basically I have four seperate ranges on one sheet (Stacked on top of each other with one row between each). These ranges are generated from another macro. I want to be able to select each of the four ranges and filter them by color (like green on top). My issue is that the length of each of the ranges will change every day when a new report is run. Basically I would like to know if it is possible to add filters to JUST the selected(Highlighted) section. My first part of the macro looks like this:

Range("A2").Select
Selection.End(xlUp).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add(Range("I2:I85"), _
xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(146, _
208, 80)
With ActiveWorkbook.Worksheets("Sheet2").Sort
.SetRange Range("A1:M160")
' This is where I assume a change needs to be made
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Is it possible to make .SetRange Range("A1:M160") not hard coded?

Thank you for your understanding with my beginner level trying to use macro recorder more efficiently haha.

 

[DanteAmor]

Try this.

Assuming that your data starts in row 1, in the column it always has data, all the blocks have a header and there is a blank row between each block.

Sub Test_Sort()
  Dim rng As Range
  For Each rng In Range("A1", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants).Areas
    With Sheets("Sheet2").Sort
      .SortFields.Clear
      .SortFields.Add(rng.Offset(1, 8), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(146, 208, 80)
      .SetRange rng.Resize(rng.Rows.Count, 9)
      .Header = xlYes
      .Apply
    End With
  Next
End Sub

 

[gress032]

This worked perfectly. I had no idea about the "Next" function. That is huge. Very much appreciate.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.