VBA - simple sort is killing me!

zookeepertx

Well-known Member
Joined
May 27, 2011
Messages
507
Office Version
  1. 365
Platform
  1. Windows
Hello all!

This should be so easy, but not for me, apparently!
I have a table of data that can be of varying lengths and widths. My current macro does what I want - up to a point; that being to transpose the data and paste it in a new group underneath the current data. But then I want to sort it and that's where my problem comes in. The new info will always be pasted into column AE, starting in the third row below the raw data. The last row of raw data is represented by "lr"; the last column of raw data is represented by "LC". Once the 2nd table is created, the last row of that data is represented by "LR2" and the last column is the new value for "LC".

I've tried NUMEROUS versions of that part of my code and get various different errors each time. It's just a SORT; it should not be this hard!! I feel stupid!
Anyway, if someone can point out what I'm doing wrong, it would make me very happy, then I could move on to the next part of the macro.

Here is the code as I have it right now. I'll Bold/Underline where the error messages appear:
VBA Code:
Sub MacroPOBI()
'JennyDrumm 082820
' MacroPOBI Macro

With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
    .DisplayAlerts = False
End With

Cells.Select
With Selection
    .WrapText = False
    .MergeCells = False
    .Font.Name = "Times"
End With

Columns("AH:AH").Delete Shift:=xlLeft

lr = Cells(Rows.count, 31).End(xlUp).Row
LC = Cells(12, Columns.count).End(xlToLeft).Column

With Range(Cells(12, 31), Cells(lr, LC)).Select
    Selection.Copy
    Cells(lr + 2, 31).Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
End With

LC = Cells(lr + 2, Columns.count).End(xlToLeft).Column
LR2 = Cells(Rows.count, 31).End(xlUp).Row

[B][U]Range(lr + 2 & ":" & LC).Sort Key1:=Range("AF" & lr.Row + 2), Order:=xlAscending, Key2:= _
    Range("AG" & lr.Row + 2), Order:=xlAscending[/U][/B]

With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .Calculation = xlCalculationAutomatic
    .DisplayAlerts = True
End With

End Sub


Here is what I have:

Capture1.JPG



Here is what I need:

Capture1b.JPG

Thank you for looking at this.

Jenny
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,120
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Just a guess but what happens if you change (I am assuming that column AG is LC)
VBA Code:
Range(lr + 2 & ":" & LC)
to
VBA Code:
Range(Cells(lr + 2, LC - 1), Cells(LR2, LC))
VBA Code:
Range("AF" & lr.Row + 2)
to
VBA Code:
Range("AF" & lr + 2)
and
VBA Code:
Range("AG" & lr.Row + 2)
to
VBA Code:
Range("AG" & lr + 2)
 

zookeepertx

Well-known Member
Joined
May 27, 2011
Messages
507
Office Version
  1. 365
Platform
  1. Windows
Range(Cells(lr + 2, LC - 1), Cells(LR2, LC))

Hmmm, then I get a "Compile error: Named argument not found" later on in that line of code; specifically at the first Order:=xlAscending . I assume that it would also happen a few words later at the second Order:=xlAscending

As for AG - I need to sort by AF first and then by AG.

Thanks!
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,120
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Rich (BB code):
Range(Cells(lr + 2, LC - 1), Cells(LR2, LC)).Sort Key1:=Range("AF" & lr + 2), Order1:=xlAscending, Key2:= _
   Range("AG" & lr + 2), Order2:=xlAscending
 

zookeepertx

Well-known Member
Joined
May 27, 2011
Messages
507
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Then I get Run-time error '1004': The sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort By box isn't the same or blank.

My first question is "the first Sort By box isn't the same" as what?
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,120
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
The code works for me if your last column is AG.
Code used for testing
VBA Code:
Sub test1()
    Dim lr As Long, LC As Long, LR2 As Long
    lr = 2
    LC = 33
    LR2 = 18
    Range(Cells(lr + 2, LC - 1), Cells(LR2, LC)).Sort Key1:=Range("AF" & lr + 2), Order1:=xlAscending, Key2:= _
        Range("AG" & lr + 2), Order2:=xlAscending
End Sub

Start

Book1
AFAG
42385
54918
68543
79659
82028
91924
104055
114523
126483
131452
144362
155217
169154
171654
181317
Sheet1


Result

Book1
AFAG
41317
51452
61654
71924
82028
92385
104055
114362
124523
134918
145217
156483
168543
179154
189659
Sheet1
 

zookeepertx

Well-known Member
Joined
May 27, 2011
Messages
507
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I'm very confused.

Where did this come from?
lr = 2
LC = 33
LR2 = 18

I also don't know where you got the numbers in your two images. I'm missing something. o_O
**********************************************************************************************************************************
And, I gave incorrect information on my 3rd attachment in my first post: I showed the second table as being like this -
Capture1b-1.JPG


When it should actually result in this -
Result table-correction.JPG

And then the sort needs to take effect to group AF by DC and then AG by STORE

Then I'll place formulas to the right of the table that will consolidate the data in the format needed.

I'm sorry I represented the desired result incorrectly in the first post; this dang thing has my head spinning!

Thank you!

Jenny
 

zookeepertx

Well-known Member
Joined
May 27, 2011
Messages
507
Office Version
  1. 365
Platform
  1. Windows
OH! I also meant to say that, when the macro freezes for the irror, I hovered over the code in a couple of places and got weird info! Here's a screenshot of it. I have absolutely NO clue what these mean!

Code errors.JPG
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,120
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Where did this come from:
lr = 2
LC = 33
LR2 = 18

What do you mean where did they come from. They are numbers to match my sample data as you didn't supplied any range information for your data or the values of the variables, the numbers above do not make any difference if you have done your variables correctly. The code line would not run if I didn't put in some numbers.

If you have formulas or data to the right of AG then your LC won't be correct in the sort.

I also don't know where you got the numbers in your two images. I'm missing something. o_O
That is because you never supplied any data that I could copy into Excel. The numbers are test data as I did not want to retype all your data. It does not matter what the numbers are to test.

Please repost a sample of your data at the stage when you are doing the sort using the forums XL2BB addin (click the XL2BB link in the Reply window for the download and instructions on how to use and install),.

The bits you have circled in your last images are the values in the cells. (LR2,LC) in your image is AM31 and that has a value of 9. As I stated above the LC is not right for the sort! At a guess by your last image it should be
VBA Code:
  Range(Cells(lr + 2, LC - 7), Cells(LR2, LC - 6).Sort Key1:=Range("AF" & lr + 2), Order1:=xlAscending, Key2:= _
        Range("AG" & lr + 2), Order2:=xlAscending

By the way I don't know why you want to use LC in the sort when you already know the column letters, you could just use....
VBA Code:
  Range(Cells(lr + 2, "AF"), Cells(LR2, "AG").Sort Key1:=Range("AF" & lr + 2), Order1:=xlAscending, Key2:= _
        Range("AG" & lr + 2), Order2:=xlAscending
 
Last edited:

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,120
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Actually, maybe you are sorting based on the whole range as I now know there is data to the right.
Post back clarification when you post your sample data using XL2BB
 

Watch MrExcel Video

Forum statistics

Threads
1,133,465
Messages
5,658,938
Members
418,477
Latest member
coutinhoicaro

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
Top