Generic sort macro for varying lengths

**s0o0y** · 10-23-2013, 02:54 PM

Hello,

I am fairly new when it comes to VBA/macros. I recorded a macro with me manually selecting rows 3:3000, then sorting alphabetically. Then I edited the code with what I picked up from the forums, trying to convert said macro into a generic sort macro that will work any active workbook, active worksheet. This macro would ignore rows 1&2 as these are header rows for my company.

Here is what I have so far:

Please Login or Register  to view this content.

When I run said macro, I get an error and debugger takes me to the line

Please Login or Register  to view this content.

Before I altered it it read Rows("3:3000").Select. Did I define LastRow properly? Do I need to change the format of the Rows function as it is now using a variable versus two integers? Getting ahead of myself, the code also used to read ActiveWorkbook.Sheet*name* something of that sort, and I changed it to Active.Sheet. Is that going to error out or did I convert it properly? I'm sure the code may be riddled with quite a few more bugs, as I did wing it and hope for the best...

Thanks for taking the time to read my problem. I will appreciate any help that can come my way,

-S

**Alf** · 10-23-2013, 03:40 PM

Be aware of a small problem that may pop up using

Please Login or Register  to view this content.

If your data starts at row 3 i.e. row 1 and 2 are empty and goes down to row 41 the above expression will define LastRow as 39 becuse this is the number of used rows.

If you do whish to know the number of the last row i.e. 41 try something like this

Please Login or Register  to view this content.

Don't know if you solved your problem as thread is marked "Solved" if not you could try

Please Login or Register  to view this content.

Alf

**s0o0y** · 10-23-2013, 04:03 PM

Hi again Alf,

It was a weird bug, as I initially posted the forum as unsolved. I had to mark this "solved" post as solved, then unsolved again before it registered properly on the forums...

Anyway, good catch, as I do want it to count possible blank rows preceding the last row. Implementing your suggestions in the code, I have:

Please Login or Register  to view this content.

Now when I run I get "Runtime error '1004': Method 'Range' of object '_Global' failed" and it takes me to line

Please Login or Register  to view this content.

As I figured earlier, I must be using the variable LastRow improperly with Range. What would be the correct syntax for this line?

Thank you,

-S

**Alf** · 10-23-2013, 05:42 PM

You are setting a range so since "LastRow" is a number (for instance 47) "Range("A3:" & 47) does not make sense to "Excel"

If on he other hand the expression was

Please Login or Register  to view this content.

this would correspond to a range "A3:A47" and now it makes sense to "Excel"

Alf

**s0o0y** · 10-24-2013, 03:56 PM

That makes sense.

I'm seeing now that my selection line must be redundant, as before the sorting is called in order it clears the sort fields and then defines it.. This is causing a problem as I noticed the sort is now working, but only sorting column A, without the other columns following the data in col A that they should correspond to.

Again, this is me wigging it and missing a crucial element, but this is how I attempted to fix my range issue:

Please Login or Register  to view this content.

where I defined LastCol earlier with:

Please Login or Register  to view this content.

I figure this code would work as I intended if LastCol was a letter versus a number, but I know from my other posts that excel can recognize a column by a numerical representation as well. I tried using Cell(LastRow, LastCol) in the range field, but again I'm missing a factor. I know that is telling Excel to look at that Cell for a 'property,' but I'm not sure how to ask it look there for the cell "address" and return that to put in the second half of my range... Can you help clear this up for me?

-S

**Alf** · 10-24-2013, 04:37 PM

As LastCol and LastRow both are numbers how about trying to use the cell properties?

Please Login or Register  to view this content.

where "Cells(3, 1)" is cell in row 3, column 1 i.e. A3.

Since you also know the upper left corner of your data cell A3 and if you data is coherent you could test the "CurrentRegion" command to see if this could be used as well. Something like this perhaps. Don't forget to dim rng as range.

Please Login or Register  to view this content.

Alf

**s0o0y** · 10-24-2013, 04:59 PM

Oh, I was using the CELL function as mentioned here, confusing myself, hah. I used your range example with Cells() function now as I already defined LastCol.

The macro now works as intended. Here is the final code for anyone else who may want to reference the working generic sort macro:

Please Login or Register  to view this content.

Again, this code ignores the first two rows, which I use as headers on my sheets. If you would want to use this code and start on a different row, you could simply change the range lines to where you please, those lines being

ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:=Range("A3"), _
.SetRange Range(Cells(3, 1), Cells(LastRow, LastCol))

Once again you walked me through one of my issues, Alf. Thank you for the help, I appreciate it =).

-S

Generic sort macro for varying lengths

LinkBack

Thread Tools

Rate This Thread

Display

Generic sort macro for varying lengths

Re: Generic sort macro for varying lengths

Re: Generic sort macro for varying lengths

Re: Generic sort macro for varying lengths

Re: Generic sort macro for varying lengths

Re: Generic sort macro for varying lengths

Re: Generic sort macro for varying lengths

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Concatenate with varying lengths and data

Best way to store lists of varying lengths

Combining Data of varying lengths

Apply Macro to varying row lengths

Generic Page Setup Macro applied to multiple workbooks with varying number of sheets

Bookmarks

Bookmarks

Posting Permissions