Any VBA aces here?

Sprogget · 4th November 2009, 12:53

I have a spreadsheet that I use to control & record all the sales & operations data in my work. It starts the year at zero rows & ends it on anywhere between 1000-2500 rows & has something like 20 columns as below:

What I'm trying & failing to do is create a macro, or more accurately modify someone else's vba code to make the rows on this sheet sort automatically, in ascending date order by columns H, then K, which are collection date & delivery date, each time a new job is added, so that the newest jobs are at the bottom of the worksheet.

Anyone handy with this kind of thing? I have looked on Mr. Excel & a few other places without success.

Sprogget · 4th November 2009, 13:14

Ah, getting somewhere. I recorded a macro, giving me this code:

Code:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 04/11/2009 by Sproggett
'
' Keyboard Shortcut: Ctrl+s
'
    Range("A6:V408").Select
    Selection.Sort Key1:=Range("H6"), Order1:=xlAscending, Key2:=Range("K6") _
        , Order2:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
        Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:= _
        xlSortNormal
End Sub

The end range I have higlighted is now causing me a problem. If I add a row to the sheet to record a new sale, it only sorts up to row 408. So my new question is how do I ensure the macro continues to include new rows as the spreadsheet expands?

Captain Gadget · 4th November 2009, 16:19

Hi Sprogget

You need to use a named range and a bit of code to define the range variably according to the data content within. The actual code varies slightly according to the actual content you want to detect.

Try googling 'create expanding range in Excel'. There are plenty of ideas, including this 'official' one:

How to create a dynamic defined range in an Excel worksheet

Hope this helps

Gadget

Sprogget · 4th November 2009, 16:52

Thx Gadget. Looks like I'll have to do a bit of reading around OFFSET...

Simonta · 4th November 2009, 22:05

Hi Sprogget.

Take a look at worksheet.usedrange which should return all the rows used in the sheet. It can be a little temperemental if new rows have been added so if it's a possibility, you could add workbook.save to the beginning of the routine. In the majority of workbooks, especially those with perfectly rectangular ranges, as yours appears to have, usedrange is reliable after save.

Alternatively, here's a function which will calculate the used range, thereby providing accuracy.

VBA Express : Excel - True Used Range in Excel

Hope this helps.

Cheers

Simon

Simonta · 4th November 2009, 22:16

Just noticed that you want to do this after a new row is added. I assume that the user has to remember to click a sort button or some such after adding a new row?

The coding gets a lot more complex but did you know that:

1. You can add a button called "New row", or similar, which would present the user with a nice form to fill in (complete with validity checks if needed),add the row then sort the sheet.
2. There are "events" in Excel which you can attach code to. One event is worksheet_changed which is called every time a change is made to the worksheet. This code could determine if a new row has been added then go ahead and sort.

Just FYI....

PS. I'm a nice guy ;-), if you fancy any of these approaches, PM me and I'd be happy to code this for you.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode