Using Sort function in macro crashes

**sfax39** · 06-03-2009, 04:56 PM

I have a report I produce where I take an XML file, sort it and run some simple formulas then sort another way and run a few more formulas then update a pivot table. The XML files I'm using have the same headers but vary in length. When I set the macro up for a particular XML file everything works fine. When I import another one (different size) I get the error message:

Run-time error ‘1004’:
Sort method of Range class failed

When I look at the code I see that the range is fixed to the XML file I used when setting the macro up. Is there a way to auto-select the table in the macro so I can run the same macro on any size XML?

The code for the first sort looks like this:

Please Login or Register  to view this content.

Any help here would be greatly appreciated.

**JBeaucaire** · 06-03-2009, 06:39 PM

Welcome to the forum, SFA. There are several ways to do what you ask.

Is your "table" really only column A as demonstrated in the macro? It appears you are sorting on multiple "keys" but only one column. That seems odd to me.

What is the width of the table? Does that change? Is it the # of rows you want to dynamically select?

Perhaps it would be easier for us to picture if you uploaded a couple of sample datasets showing the table in a couple of different forms...

Click on GO ADVANCED and use the paperclip icon to upload some some sample data, point out the real keys you want to use in each and how they were identified. Perhaps even before/after examples.

**sfax39** · 06-04-2009, 10:30 AM

Thank you.
i know it looks like only column A is selected in the macro but if you look closer the range is actually "A1:AI76". it's hard to see the difference between the number 1 and the alpha character I. the width of the column stays the same, always ends with AI. only the number of rows changes. yes, i believe it's the number of rows i want dynamically selected.
the code shows 76 rows. it can get as high as 7000 depending on the sales center. attached is an example.

**JBeaucaire** · 06-04-2009, 12:00 PM

Let's try it this way:

Please Login or Register  to view this content.

**sfax39** · 06-04-2009, 01:15 PM

That is fantastic! thank you!
one more question:
i'm finding the same problem with copying the formulas that i insert. for example, the code below describes the three formulas i insert. i type the formula in the first cell and double-click to auto fill the formula down to the last cell. you can see that repeated three times below.

example:
Selection.AutoFill Destination:=Range("F3:F4138")

again it specifies a range. can i use the same piece of code you provided above to copy the formula in any size XML file? otherwise when i move from a large file to one with fewer rows the formula extends far below the new XML.

Please Login or Register  to view this content.

**JBeaucaire** · 06-04-2009, 01:58 PM

Like this, much quicker, no autofilling needed if you already know how many rows you want to insert into, do it all in one step:

Please Login or Register  to view this content.

The code in blue is another way to remove formulas, just re-insert the "values".

Notice, we didn't select anything to do this work? Much faster.

**sfax39** · 06-04-2009, 03:44 PM

Thanks again JBeaucaire. i believe i'm ready to publish this report. i appreciate you're help.

**JBeaucaire** · 06-04-2009, 03:47 PM

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].

(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)