Sorting Excel sheet by VBA

[rickalty]

Hi everyone, can anyone help me please?

I have an Excel spreadsheet. Rows 1 through 7 are used by column headers and some general information about the info that is displayed. Rows 8 and up contain the actual data.

New data is entered into the worksheet from a form. The form, currently, appends the new row of data across the bottom of the existing data. I'd like the same button that does that to then resort the worksheet so that it's listed in alphabetical order according to column B. (Column A is blank)

I have tried it with this code:

Please Login or Register  to view this content.

but that doesn't work. Can anyone help me out?

Richard

[Trebor76]

Hi Richard,

Let me know if the following does the trick:

Please Login or Register  to view this content.

HTH

Robert

[VBA Noob]

Or without selecting try

Please Login or Register  to view this content.

VBA Noob

[royUK]

Excel will sort the whole data including the data above if you use currentregion. Specify the Range to sort, I have set the last Column as D amend to fit your table.

Please Login or Register  to view this content.

[rickalty]

Thanks, everyone. I got it to work. The problem I was having turned out to be that the sheet "Products" isn't the active sheet while the app is running. putting in a line to make it so did the trick. Here's the actual code that's working as required.

Please Login or Register  to view this content.

Thanks for everyone's help.

Richard

[rickalty]

Hi, can someone explain why this:

Please Login or Register  to view this content.

works, but this:

Please Login or Register  to view this content.

doesn't?

Surely I should be able to sort the range just by .Sort, but to make it work I have to .Select, then do Selection.Sort?

Richard

[Leith Ross]

Hello Richard,

Try activating the worksheet before you sort...

Please Login or Register  to view this content.

Sincerely.
Leith Ross

[Ikaabod]

Just FYI, this can be done without activating the sheet. The problem is that the macro is trying to sort data in an inactive sheet by a cell in the active sheet. To solve this (without activating the sheet to sort) you could do something like this:

Please Login or Register  to view this content.

[royUK]

My code doesn't need to select the sheet, nor does it use any looping.