View Full Version : Run-time error 1004

10-03-2017, 07:49 AM

This is a super basic error but I am trying to sort a column from A to Z from top to bottom.

Here is the code below:

Worksheets("information Sort sheet").Range("A:A").Sort key1:=Range("A:A"), order1:=xlAscending

Is there something of which I am missing here?

10-03-2017, 08:39 AM
Specify a single cell as the Key

Worksheets("information Sort sheet").Range("A:A").Sort key1:=Range("A1"), order1:=xlAscending

Then try this

Worksheets("information Sort sheet").Range("A:A").Sort Order1:=xlAscending
Also check the spelling and capitalizationof the Sheet name. Look for leading and trailing spaces.

I often run this Macro when developing a Workbook Project
Sub TrimSheetNames()
Dim Sht As Object

For Each Sht In ActiveWorkbook.Sheets
Sht.Name = Trim(Sht.Name)
End Sub

10-03-2017, 08:49 AM
Worksheets("information Sort sheet").Range("A:A").Sort key1:=Cells(1, 1), order1:=xlAscending

I am still getting the same area with the code above.

10-04-2017, 07:31 AM
Apologies. I mistyped, I meant to type "error" as opposed to "area". Thanks

10-04-2017, 08:07 AM
Lots of times I'll record a macro that does what I want, and then clean it up and generalize it

Option Explicit
Sub Macro1_AsRecorded()
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A2:A23") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A1:A23")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
End With
End Sub

Sub Macro1_Generalized()
Dim r As Range, r1 As Range

With ActiveWorkbook.Worksheets("Sheet1")
Set r = .Cells(1, 1).CurrentRegion.Columns(1)
Set r1 = r.Cells(2, 1).Resize(r.Rows.Count - 1, r.Columns.Count)

With .Sort
.SortFields.Add Key:=r1, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SetRange r
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
End With
End With
End Sub