Get Rid Of Static Range in Sort Macro - Replace with variable Range

[sdinyer]

I have a Macro that selects a range of rows/columns, which will change, daily.

I recorded a second Macro, a Sort Macro, and the stupid thing has a Static Range that is set and won’t match any new selection when run. I was going to put the two Macros together any way. So is there any way to get rid of the Sort Ranges in the Sort Macro and replace them with the new selection that would be generated by the first Macro (GoToEndOfData)??

“First Macro”


Sub GoToEndOfData()
'
' GoToEndOfData Macro
' Goes to End of Data ThenLeft arrow to Last Field

Application.Goto Reference:="EndOfData"

ActiveCell.Offset(0, -1).Select

Range(Cells(17, 1), ActiveCell).Select

End Sub


“Second Macro”


Sub SortNewToOld()
'
' SortNewToOld Macro

'
ActiveWorkbook.Worksheets("Entries from 10-6-09").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Entries from 10-6-09").Sort.SortFields.Add Key:= _
Range("B17:B246"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
:=xlSortNormal
With ActiveWorkbook.Worksheets("Entries from 10-6-09").Sort
.SetRange Range("A17:H246")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

Thank You,
Steve

 

[sdinyer]

I guess I'll just have to answer my own questions.
This is the second one that no one else bothered.

It took me about a whole day of searching the web and looking at tutorials, looking for some info.. For those who are interested, the below seems to be the solution but I really don't know why. I can not find any syntax rules that make any sense for VBA. It's sort of hard to find rules to follow. When I first recorded the Macro you would think that because it is just supposed to record keystrokes, it would leave out the Cel Ranges because when recorded the selection was already selected. I think this is STUPID! So I had to edit it with some nonsensicle code to FIX that Stupidly. (but I cease ranting, now).

I've seen, no lie, about 20 other requests for the exact same problem as mine. Every solution that was given, was a TOTALLY convoluted solution that made no sense, and didn't work for me, not even when adjusted for my situation. What I was looking for ended up being similar to what I finally figured out but still I am not sure why this syntax works and somethings quite simpler did not. I have tried many examples of code with similar needs that looked like it should work then did not. In all the various rows of code I looked at, I could see no reason why that the one that finally worked did. It really looked like it wouldn't but I tried it along with the hundreds of others. Some that didn't work, seemed perfectly correct in other code I've seen.

If you compare my first post to this, you'll see where I was and where I ended up. The code in Magenta is what I changed. I took out the B246 from ("B17:B246") because the B17 is the first Cell in the "sort by" column. I did that first and it worked,,,, don't know why.

The second line,,, I thought was what I needed i.e. "tell the Macro to use the current selection NOT the static RANGE " So this is what I thought I needed BUT the syntax makes NO SENSE WHAT SO EVER. It seems to work though. I just copied it from a totally non related macro because it seemed to match a similar need. Imagine my surprise when the **** thing worked. By that moment I was so frazzled, I didn't know what to think. I hope this helps someone because it seems to be something that happens often. I still have to carefully check all the results to make sure it doesn't positively screw something up but after a quick check it looks solid. Thanks for checking this out. Good luck!
Steve


Sub SortNewToOldFromWeb()
'
' SortNewToOld Macro New
'
ActiveWorkbook.Worksheets("Entries from 10-6-09").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Entries from 10-6-09").Sort.SortFields.Add Key:= _
Range("B17"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
:=xlSortNormal
With ActiveWorkbook.Worksheets("Entries from 10-6-09").Sort
.SetRange Range(Selection, Selection.End(xlDown))
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub