Macro help - use to work now getting an error

[lwilt]

I'm using a macro to transpose my data. I realized that the 3rd column isn't sorted correctly so when it transpose it the data ends up out of place. So I sorted the file by the first 3 columns in order to keep the grouping together and to now have the 3rd column sorted in descending order but its causing my macro to get an error message now about not being able to run. If I don't sort it runs fine but I don't get the desired outcome. here is the macro:

Sub TransposeQandP()
Dim X As Long, Ar As Range
With Range("A1:A" & Cells(Rows.Count, "B").End(xlUp).Row).SpecialCells(xlBlanks)
For Each Ar In .Areas
For X = 1 To Ar.Count
Ar(1).Offset(-1, Cells(Ar(1).Offset(-1).Row, Columns.Count).End(xlToLeft). _
Column).Resize(, 2).Value = Ar(1).Offset(X - 1, 2).Resize(, 2).Value
Next
Next
.EntireRow.Delete
End With
End Sub

 

[JoeMo]

What is the error message and which line is highlighted when you get it?

 

[lwilt]

I get an error message about not having enough Ram to complete the process even though that excel doc is the only thing open when doing it. But if I run it without sorting it works fine every time so I'm assuming it's not really an issue with Ram but with the macro itself not liking the fact that I sorted the file before running it.

 

[JoeMo]

I get an error message about not having enough Ram to complete the process even though that excel doc is the only thing open when doing it. But if I run it without sorting it works fine every time so I'm assuming it's not really an issue with Ram but with the macro itself not liking the fact that I sorted the file before running it.

If the macro compiles and runs ok before you sort, it's hard to see why a memory issue arises when you simply sort some of the data. Have you tried quitting Excel and restarting it?

 

[lwilt]

I have and it didn't work. I even tried copying the part I'm doing the macro on into a new excel file with no other programs open and running the macro on the sorted data and it still gave me an error....then I immediately ran the original non sorted and works fine.

really confused with what is happening with it

 

[lwilt]

Is there a way to attach a file so someone can see if I just goofed up somewhere because it's make no sense how sorting is messing this up

 

[JoeMo]

Is there a way to attach a file so someone can see if I just goofed up somewhere because it's make no sense how sorting is messing this up

Can you post some sample data before and after sorting for the 3 columns?

 

[lwilt]

playing with the sorting it appears I have found the problem....it seems to sort in a weird way.

sku	sku1	Q	P
AF121C60	AF121C60
AK18025	AK18025	6	9
	AK18025	12	8.5
	AK18025	24	8
	AK18025	36	7.25
AB411FIR	AB411FIR	3	54
	AB411FIR	6	52
	AB411FIR	12	50
	AB411FIR	24	48
AE120AUS	AE120AUS	3	60
	AE120AUS	6	56.25
	AE120AUS	12	53.13
	AE120AUS	24	50
AY521	AY521	15	156
	AY521	9	159
	AY521	6	162
	AY521	3	165
	AY521
	AY521
	AY521
	AY521
	AY521
	AY521
	AY521
	AY521
	AY521
	AY521
	AY521
AY522	AY522	3	183.5
	AY522	6	179
	AY522	9	169
	AY522	15	163
	AY522
	AY522
	AY522

<colgroup><col span="2"><col><col></colgroup><tbody>
</tbody>

How should I sort the 4 columns so that columns A and B look basically the same but it puts column c in descending order?

 

[JoeMo]

If you sort column C, there's no way to keep you data together w/o affecting cols A & B.

You might be better off starting a new thread explaining in detail what you want to accomplish with before and after examples of your data.

 

[lwilt]

if I removed column 1 from the data and just kept the other 3 columns what would I need to change in the macro for it to work the same way as before?