Sorting part of a spreadsheet alphabetically

Question

I am trying to sort one section of the data I have alphabetically based upon the company names in one row.

The relevant columns are the company name in column 7 and the specification number in column 9. The script that I wrote so far finds the highest and lowest column with the correct specification number and then inserts all of the companies data in a new row following the last row with the right specification.

I then want to sort only the rows with that specification number by the company name so that the company name is in alphabetical order. This must sort the full row with all of the company's info, not just the name.

The bit of code that I had tried to use to accomplish this is as follows:

Range(Cells(firstSpec, 7), Cells(lastSpec, 7)).Sort Key1:=Target, Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

This just gives me a runtime error of 1004

What do I need to do to make this sort properly?

Turn on the macro recorder, do the sort manually, and go look at what it made. — Alan Waage

Gary's Student Gary's Student · Accepted Answer · 2014-05-08T17:18:02

Good news!

You line of code works in this context:

Sub qwerty()
    Dim firstSpec As Long, lastSpec As Long
    Set target = Cells(1, 7)
    firstSpec = 1
    lastSpec = 3
    Range(Cells(firstSpec, 7), Cells(lastSpec, 7)).Sort Key1:=target, Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

If you are getting errors, check the values for firstSpec, lastSpec and target

Sorting part of a spreadsheet alphabetically

1 Answers