• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

variable to capture/represent ActiveRange

r121a947

New Member
I am very new to VBA. I believe there is an easy way to get this info, but I just have not found a way.

I want to select a portion of a worksheet (~30 rows by 13 columns; the rows are different each time, the columns are constant), and I want to Sort the rows on column B.

I have recorded a macro that works, but I cannot get it to Sort a different selected range. I have tried various versions of ActiveRange and Selection, but have not found the answer.

Thank you for your interest and your assistance.
 

salim hasan

Member
suppose that you data starting from the cell A1
try this macro
Code:
Option Explicit
Sub SORT_ME()
Range("A1").CurrentRegion.Sort key1:=Range("B1"), Header:=1
End Sub
 

Attachments

p45cal

Well-Known Member
1. What are the names (letters) of the 13 columns?

Depending on your answer, it will be something like this:
Code:
Sub blah1()
With ActiveSheet.Sort
  .SortFields.Clear
  .SortFields.Add2 Key:=ActiveSheet.Range("B1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  .SetRange Intersect(Selection.EntireRow, Columns("A:M"))
  .Header = xlNo
  .MatchCase = False
  .Orientation = xlTopToBottom
  .SortMethod = xlPinYin
  .Apply
End With
End Sub
or:
Code:
Sub blah2()
With ActiveSheet
  Set RngToSort = Intersect(Selection.EntireRow, .Columns("A:M"))
  RngToSort.Sort Key1:=Intersect(RngToSort, .Columns("B")), order1:=xlAscending, Header:=xlNo, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod:=xlPinYin, DataOption1:=xlSortNormal
End With
End Sub
It looks like you only want to sort part of a table.
Select ANY single block of cells, any width, but the rows must be right, within your columns or not and (with the code above) the 13 columns A:M will be sorted according to column B ascending.
Don't include the headers as they will be sorted too!
 
Last edited:

r121a947

New Member
suppose that you data starting from the cell A1
try this macro
Code:
Option Explicit
Sub SORT_ME()
Range("A1").CurrentRegion.Sort key1:=Range("B1"), Header:=1
End Sub
Thank you for your suggestion.

If I understand correctly, I would have to supply the starting cell of the active range for each new sort . . . Yes?

If so, that would require more work than just sorting . . .
 

r121a947

New Member
1. What are the names (letters) of the 13 columns?

Depending on your answer, it will be something like this:
Code:
Sub blah1()
With ActiveSheet.Sort
  .SortFields.Clear
  .SortFields.Add2 Key:=ActiveSheet.Range("B1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  .SetRange Intersect(Selection.EntireRow, Columns("A:M"))
  .Header = xlNo
  .MatchCase = False
  .Orientation = xlTopToBottom
  .SortMethod = xlPinYin
  .Apply
End With
End Sub
or:
Code:
Sub blah2()
With ActiveSheet
  Set RngToSort = Intersect(Selection.EntireRow, .Columns("A:M"))
  RngToSort.Sort Key1:=Intersect(RngToSort, .Columns("B")), order1:=xlAscending, Header:=xlNo, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod:=xlPinYin, DataOption1:=xlSortNormal
End With
End Sub
It looks like you only want to sort part of a table.
Select ANY single block of cells, any width, but the rows must be right, within your columns or not and (with the code above) the 13 columns A:M will be sorted according to column B ascending.
Don't include the headers as they will be sorted too!
Thank you for your interest and your suggestions.

The second example looks like it will be the answer. I will try it and get back to you.

Thanks, again.
 

p45cal

Well-Known Member
The second example looks like it will be the answer.
Both examples do exactly the same thing and behave in the same way, the only difference is that the first uses ActiveSheet.Sort (introduced, I think, in Excel 2007) while the second uses Range.Sort which is an older sort method.

If I understand correctly, I would have to supply the starting cell of the active range for each new sort . . . Yes?
If so, that would require more work than just sorting . . .
That's what I thought you were asking for:
I want to select a portion of a worksheet (~30 rows by 13 columns; the rows are different each time, the columns are constant), and I want to Sort the rows on column B.
<snip>
I cannot get it to Sort a different selected range. I have tried various versions of ActiveRange and Selection
I've mentioned this before: what do you mean by "ActiveRange"?
 

r121a947

New Member
Thank you for your interest and your suggestions.

The second example looks like it will be the answer. I will try it and get back to you.

Thanks, again.

Works perfectly! It's saving me a lot of time.

Thank you for your interest, expertise, and assistance.
 
Top