How To Sort a GridView Column When a Header Is Clicked?

Variable sort and macros

• QUESTION: I would like to sort a range starting at P5 through S5 and down the value of P4. If the value of P4 is 10, then my sort range would be P5 through S15. Would you please tell me what the Macro command would be to accomplish this function? I don't know how to get the value of "P4" into the sort function. ANSWER: Range("P5:S5").Resize(Range("P4").value).Sort key1:=... does that help? ---------- FOLLOW-UP ---------- QUESTION: So far I have not been able to figure it out, I don¡¯t know where to insert the resize command into the VBA. This is what I am working with: Range("P5:S7").Select ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("Q5:Q7"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Sheet1").Sort .SetRange Range("P5:S7") .Header = xlGuess .MatchCase = False .Orientation = xlTopToBot .SortMethod = xlPinYin .Apply Sometimes I have a hard time trying to explain my objective. Perhaps there is a better way to accomplish my goal. Here is what I am trying to do. I have a vertical list of names, i.e., John Smith, let say d1 through d15. To the right of the names, using the ¡°find¡± command I separate the first and last name, placing first names in one column and the last in the other. I then take the first letter of the first name and using the concatenate command I end up with the First initial and the last name. I then use this in Data validation list command, i.e., J. Smith. All of this works well, I have this set up so that others can add or delete names as people come and go. The problem is that I need to alphabetize the names using a macro function. Reason for the macro is because it will be more than just me adding and removing names. If John Smith is the third name in the list and this person is no longer present, I need to remove his name and resort to remove the space between the names. If I sort A-Z there will be a blank space at the top of the list. If I sort Z-A then there is no space at the top but it is reverse alphabetized. Is there a way, using a macro function, that I can delete a name, sort A-Z, and have no empty spaces at the top of the list?

• Answer:

  Blanks ALWAYS get sorted to the botno matter what the sequence. You could use this code: Range("P5:S7").Resize(Range("P4").Value).Select ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("Q5:Q7"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Sheet1").Sort .SetRange Range("P5:S7").Resize(Range("P4").Value .Header = xlGuess .MatchCase = False .Orientation = xlTopToBot .SortMethod = xlPinYin .Apply