Recorded sorting macro won't sort subsequently added rows

[DistantDrums]

Hi, I'm a new member having an issue with a custom sort that I've recorded to a macro. Btw, I'm not too experienced with Excel (much less, macros), but I've managed to get almost what I want. Here's my hopefully easy-to-fix problem:

I have five columns within an ever-expanding, regularly-updated document, and I want to create some buttons that will execute various custom sorts, some with several levels, etc. Well, that part was easy. I recorded the first process and made a button for the first custom sort. Works perfectly... for the way everything is at the moment. The problem is, as soon as I add any other row(s) at the bottom, the recorded macro doesn't "see" them, and pressing the button will fail to include and sort the new data.

I thought I had found the solution when I recalled that I had forgotten to wait until recording had begun to do the Select All (by clicking the top-left corner of the document/cells). But even when I do start the recording before clicking the Select All, the macro will still only sort what's currently there, and will be impotent to deal with anything added subsequently.

In other words, when I select "all" in a document that happens to currently have 50 rows, Excel seems to be interpreting that as meaning "select 50 rows" rather than "select all" (meaning, "all, regardless of the figure"). How do I get it to include newly added rows in the sort, rather than being stuck on the fixed range the document had at the time of recording? Thanks for any insights.

- Bob K.

 

[DistantDrums]

Woops, I should add, I'm using Excel 2010 on Vista 64-bit.

 

[ColinKJ]

Hi Bob,

Could post a copy of your code.

 

[DistantDrums]

Hey there, Colin -

Ah, see, I'm so new at this - I have to fish around to even find where the code is...

Okay - found it (now it's just a matter of whether the indentations will paste correctly - probably not)... Here goes:

------------------------------------------

Sub CHORDS_BEAT_NOTES()
'
' CHORDS_BEAT_NOTES Macro
' CHORDS_BEAT_NOTES
'

'
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("B1:B3948" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("E1:E3948" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("C1:C3948" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("D1:D3948" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("F1:F3948" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A1:K3948")
.Header = xlGuess
.MatchCase = True
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

-------------------------------------------

- Bob K.

 

[ColinKJ]

Hi Bob,

Next time you want to post your code, use the Wrap Code button (#)which you will see in the tool bar above where you're typing your post, then copy and paste your code between the Code symbols that appears.

Looking at your code, you recorded it when you had 3948 rows of data, to cater for a varying number of rows, I've added the following line to your code:

R = ActiveWorkbook.Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

Which calculates the last used row in column A, and puts it into variable R, before carrying out the sort, I've then changed your code and replaced every instance of 3948" with " & R

Sub CHORDS_BEAT_NOTES()
'
' CHORDS_BEAT_NOTES Macro
' CHORDS_BEAT_NOTES
'
'
R = ActiveWorkbook.Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("B1:B" & R _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("E1:E" & R _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("C1:C" & R _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("D1:D" & R _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("F1:F" & R _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A1:K" & R)
.Header = xlGuess
.MatchCase = True
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

I haven't tested it, but it should be ok.

 

[DistantDrums]

Oh, man - works like a charm! Thanks, Colin. It now includes and correctly sorts all newly-added rows!

This forum is going to be a great resource, I can tell right now. I've bookmarked some good VB "beginner's tutorial" websites too, so I can try to study up.

Thanks again for your help (and for the tip about Wrap Code).

- Bob K.