Runtime error 1004. Select method of range class failed. in VBA

Question

I have created a form that adds information to a sheet. The data is added to the sheet by clicking an "ADD" button. Once added to the sheet, It then selects all data in the sheet to sort and format.

The Workbook is defined as WB set to ThisWorkbook The Worksheet is defined as WS and set to WB.Worksheets("Transactions")

The data is added without any issue, However when trying to select all data to format, i now get the "runtime" error.

In the code below, i = 28

With WS
    .Range("A2:F" & i).Select <<<---- on this line
    .Range("F" & i).Activate
    .Sort.SortFields.Clear
    .Sort.SortFields.Add2 Key:=Range("C1:C" & i), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal  ' sorts by date
    .Sort.SortFields.Add2 Key:=Range("B1:B" & i), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal  ' sorts by type
    .Sort.SortFields.Add2 Key:=Range("D1:D" & i), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal ' sorts by income or outgoing

' Further sorting and formatting continues below ......

End with

First I removed the variables from the range selection and used

With WS
    .Range("A2:F28").Select 
    ..........

End with

However this results in the same error.

I also tried setting WB to ActiveWorksheet instead of ThisWorksheet, in case it was trying to select cells from a different open workbook. However it has just written the data to the correct worksheet

I then tried to remove the use of .select

With WS
   With .Range("A2:F" & i)
    '.Range("F" & i).Activate
        .Sort.SortFields.Clear
        .Sort.SortFields.Add2 Key:=Range("C1:C" & i), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal  ' sorts by date
        .Sort.SortFields.Add2 Key:=Range("B1:B" & i), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal  ' sorts by type
        .Sort.SortFields.Add2 Key:=Range("D1:D" & i), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal ' sorst by income or outgoing
    End With
End With

But this creates an error "Unable to get the Sort property of the Range class"

https://stackoverflow.com/a/3902841/8422953 – braX Nov 30 '22 at 10:31 — braX, Nov 30 '22 at 10:31

score 1 · Accepted Answer · answered Nov 30 '22 at 10:39

(1) The error you mention is because you cannot use Select on a range of a non-active sheet. However, you don't need to.

(2) The Sort error is a little tricky. You nest 2 With-statements. In that case VBA will first look to the "inner" With-statement, in your case the Range. Now a Range has a Sort-method and the VBA runtime thinks you want to access that. However, that sort is a (one-line) method that you can call, see https://learn.microsoft.com/en-us/office/vba/api/Excel.range.sort
However, the code that you have is valid for a Sort-Object. This Sort-object exists for a Worksheet, not a Range, see https://learn.microsoft.com/en-us/office/vba/api/Excel.range.sort

So either you remove the inner With-clause to that the Sort-object of the sheet is used or you call the specific Sort-method for a Range.

Runtime error 1004. Select method of range class failed. in VBA

1 Answers1