A macro with Sort Method of Range Class issue

Question

I have a little problem with the macro below - I use data form to add or delete records. When I add a record, it works, but when I delete a record it doesn't work - the error comes up:

"Error 1004 - Sort Method of Range Class failed"

    Dim sourceSheet As Worksheet
    Set sourceSheet = ActiveSheet

    Application.ScreenUpdating = False
    SHEETS("STUDS").Visible = True
    SHEETS("STUDS").Select
    ActiveSheet.ShowDataForm
    Columns("A:H").Select
    Range("A1:H5").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, 
        Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Range("A1").Select
    SHEETS("STUDS").Visible = xlVeryHidden
    Call sourceSheet.Activate

Can anyone help please ?

This is what came up highlighted as a problem:

Range("A1:G11").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

Each time I add or delete a records, the Range changes, but macro code stay the same (A1:G11) - it does not change with Range

I have tried new and modified code but it doesn't work either

    Dim sourceSheet As Worksheet
        Set sourceSheet = ActiveSheet

    Application.ScreenUpdating = False
    With Worksheets("STUDS")
        .Visible = True
        .ShowDataForm

        Dim LastRow As Long
        LastRow = .Cells(.Cells.Rows.Count, "A").End(xlUp).Row 'find last used row in column A

        .Range("A1:H" & LastRow).Sort Key1:=Range("A1"), Order1:=xlAscending, 
        Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, _
        Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
        .Range("A1").Select 'only needed if you want to jump to the top
        .Visible = xlVeryHidden
    End With
    Application.ScreenUpdating = True

End Sub

The error msge was:

The sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort by Box isn't the same or blank

(the first Sort By box is not the same or blank) Pls help :)

You should probably know whether or not your data block has a header. Don't rely upon xlGuess. — , Mar 12 '18 at 08:33
You might want to read and follow: [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) to speed up your code and make it less vulnerable to errors. — Pᴇʜ, Mar 12 '18 at 09:08
Thank you Jeeped - it worked. Just one more thing ... Each time I delete a record the Range changes, but the macro code stays the same covering the old range. Can you help to fix this too, please? — Miles, Mar 12 '18 at 09:12
Thank you PEH ... it looks good so far .. as mentioned before, Range changes when record deleted. How to make macro so flexible that actual Range changes when record added or deleted .... — Miles, Mar 12 '18 at 09:13
Actually it did not work again ... Worked fine when I deleted one record, but when I deleted another one - the same error came up. I see that the problem is that the Range changes when a record deleted but macro does not follow that change. — Miles, Mar 12 '18 at 09:16
@Miles I meant you should read the link I gave you because using `.Select` is a bad practice and you should really avoid using them. You need to determine the last used row eg by `Dim LastRow As Long: LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row` which you can use in your range then `Range("A1:H" & LastRow)` — Pᴇʜ, Mar 12 '18 at 09:17

Pᴇʜ · Accepted Answer · 2018-03-12T09:33:12.367

0

This is a way to avoid using .Select and finding the last used row to dynamically change the range.

Dim sourceSheet As Worksheet
Set sourceSheet = ActiveSheet

Application.ScreenUpdating = False
With Worksheets("STUDS")
    .Visible = True
    .ShowDataForm

    Dim LastRow As Long
    LastRow = .Cells(.Cells.Rows.Count, "A").End(xlUp).Row 'find last used row in column A

    .Range("A1:H" & LastRow).Sort Key1:=.Range("A1"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, _
        Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    .Range("A1").Select 'only needed if you want to jump to the top
    .Visible = xlVeryHidden
End With
sourceSheet.Activate 'call is deprecated and not needed
Application.ScreenUpdating = True 'Don't forget to reactivate updating!

Please note that I used a With statement. This means everything starting with a dot refers to the worksheet specified in the With statement:

With Worksheets("STUDS")
    .Visible = True
    .Range("A1").Select
End With

is the same as

Worksheets("STUDS").Visible = True
Worksheets("STUDS").Range("A1").Select

You should always specify in which worksheet a range is. If you don't do this VBA assumes that the ActiveSheet was meant.

An alternative would be to define a variable for a sheet

Dim ws as Worksheet
Set ws = ThisWorkbook.Worksheets("STUDS")

ws.Visible = True
ws.Range("A1").Select

Remember don't use Range without specifying in which worksheet the range is.

edited Mar 12 '18 at 09:33

answered Mar 12 '18 at 09:22

Pᴇʜ

56,719
10
49
73

Thank you PEH .. I understood .. Now when I use your code and add or delete a record this is msge I've got: "The sort reference is not valid. Make sure that it's within the data you want to sort and the first sort by box isn't the same or blank" . (it is not the same and it is not blank) .. I will update my post and show you the code .... – Miles Mar 12 '18 at 09:53
If your data has a header you key is probably `Key1:=.Range("A2")` instead of `A1`? And make sure you have a `.` before the range otherwise it does not reference the worksheet in the `with` statement! I can only insist in "*No `Range()` without referencing in which worksheet the range is*" If that doesn't change anything can you provide a sample data set that causes the error? I cannot reproduce it. – Pᴇʜ Mar 12 '18 at 10:14
Furthermore when I add new record it sits outside the list (outside range) and I still get the msge: Sort method or range class failed - then I had to extend the list to be able to test again ...and again error .. Pls help – Miles Mar 12 '18 at 10:19
Ok - thank you ... I will try to change as per your suggestion and then, if no success will send you data sheet so u can reproduce ... Thanks hips PEH – Miles Mar 12 '18 at 10:20
Should be just the missing dot before `Range`. Both adding and deleting works perfectly with the code in my answer. Everything gets sorted. – Pᴇʜ Mar 12 '18 at 10:30
Hi PEH - here is a link to the file -error happened when I added a couple (or three) records ... https://1drv.ms/x/s!Av_nIf8pouuht2GrlexJQNN9BzO2 Please help if you can or if you have some time to spend on this - I am just stack :(. Macro is assigned to the button in Sheet1 – Miles Mar 12 '18 at 10:44
Please see the link .. Everything works while you add one or two records or when you delete one or two .. then the error comes up ... and my new added record sits outside List (and Range) – Miles Mar 12 '18 at 10:48
@Miles link is not valid! cannot download. – Pᴇʜ Mar 12 '18 at 11:04
Sorry about that .. not sure what could be the issue .. here is another one: https://1drv.ms/f/s!Av_nIf8pouuht2LO9dSd77SDJ_f3 I have clicked on it and it works ... – Miles Mar 12 '18 at 11:38
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/166688/discussion-between-miles-and-p). – Miles Mar 12 '18 at 12:25
PEH, you need to add https:// in front of the link to make it working. Let me know please if it is still the problem ... – Miles Mar 12 '18 at 12:42
@Miles of course I did that and I can access onedrive but it sais "document not available anymore" don't know why but got it working in a incognito window – Pᴇʜ Mar 12 '18 at 12:46
It is past midnight here in Sydney .. I will have to leave our discussion now, but will try to resume tmrw morning or a/noon, depends on your availability. I am very thankful for your help, PEH. It means a lot ... Kind regards – Miles Mar 12 '18 at 13:15
@Miles Which Excel version are you using? I guess the issue is that the `.ShowDataForm` interfers with your data *formatted as table*. So if I remove the *format as table* it works. Also if I extend the format as table by +1 row (so that it covers all your data) it works too in my Excel 2016 – Pᴇʜ Mar 12 '18 at 13:16
2003 version ... well ... that could be the problem – Miles Mar 12 '18 at 13:16
Well probably that's it. 2003 is out of Microsoft's support since a while. Remove the format as table I suggest – Pᴇʜ Mar 12 '18 at 13:17
Yeah .. I noticed... it works in 2003 too .. until I delete one or two records ..and then again, the same problem My data is formatted like a List I've had a problem to create it as a Table ...I could not sort out what to enter as Row input cell and what as a Column input cell ... Feel bad ... – Miles Mar 12 '18 at 13:17
2003 is not very safe with the new format xlsx and xlsm. Not everything is compatible. – Pᴇʜ Mar 12 '18 at 13:18
I could not sort out what to enter as Row input cell and what as a Column input cell ... So I entered my data as a List .... – Miles Mar 12 '18 at 13:23
Sorry, didn't understand your last comment. But if you remove the list and use just a normal worksheet it works as expected – Pᴇʜ Mar 12 '18 at 13:26
1

OMG ... Yes, it works as on simple sheet ... what a solution ..Ha ... I feel crazy completely .. I know, it is not quite recommended working in excel 2003 but I have to as I am developing some kind of calculator/estimator and it has to work in all excel versions ... starting from 2003. Thank you so much PEH, I truly appreciate your help. Once again you are definitely a guy who can solve anything. Thank you! – Miles Mar 12 '18 at 13:37
Gladly we got this hard one solved finally. So please mark the question as solved, so no other people need to stick their heads into it. – Pᴇʜ Mar 12 '18 at 13:47

A macro with Sort Method of Range Class issue

1 Answers1