Setting a SetRange to the highlighted selection

gress032

New Member
Joined
Aug 21, 2019
Messages
4
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.


 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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.

Code:
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
 
Last edited:
Upvote 0
I'm glad to help you. Thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top