Newbie VBA coder struggling with a Run-time error '1004'

[lvizcarra]

I have a fairly simple macro built that is supposed to delete row 2, bold format font in row 1, auto fit column widths, and sort by column. The number of rows in the worksheet will vary, but the number of columns are the same every time. I've done a lot of searching in forums for the 1004 error, but nothing I try seems to work. Here is my code:

    Rows("2:2").Delete Shift:=xlUp
    Rows("1:1").Font.Bold = True
    Columns("A:A").EntireColumn.AutoFit
    Columns("B:B").EntireColumn.AutoFit
    Columns("C:C").EntireColumn.AutoFit
    Columns("D:D").EntireColumn.AutoFit
 
    With ActiveSheet.Sort
        .SortFields.Add Key:=ActiveSheet.Range("A1"), Order:=xlAscending
        .SortFields.Add Key:=ActiveSheet.Range("B1"), Order:=xlAscending
        .SortFields.Add Key:=ActiveSheet.Range("C1"), Order:=xlAscending
        .SortFields.Add Key:=ActiveSheet.Range("F1"), Order:=xlAscending
        .SetRange Range("A1").CurrentRegion
        .Header = xlYes
        .Apply
    End With

Thanks in advance!

 

[Logit]

.
I deleted the command .Apply ... which prevented the Error Code message from appearing. The first part of the macro
ran as expected. The sorting portion did not seem to do what you were intending.

I ran the RECORD MACRO and went through the motions of sorting Cols A:F in ascending order. That macro is listed below and
does sort the data in ascending order :

Sub Macro1()
'
' Macro1 Macro
'


'
    Range("A1:F20").Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A1"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:F20")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

 

[lvizcarra]

Thanks for the quick reply, Logit. I deleted .Apply as you suggested, and while it runs without error, the macro doesn't sort column A. I need the macro to sort columns A, B, C, F in that order.

The only issue I see with the code you got by running RECORD MACRO is that my worksheet will sometimes contain more than 20 rows of data. That's why I used CurrentRegion. Is there another way to account for varying number of rows?

Thanks.

 

[Fluff]

You can modify a recorded macro like

Sub Macro1()
'
' Macro1 Macro
'

'
    With ActiveSheet.Sort
      .SortFields.Clear
      .SortFields.Add key:=Range("A1") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
      .SortFields.Add key:=Range("B1") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
      .SortFields.Add key:=Range("C1") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
      .SortFields.Add key:=Range("F1") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
      .SetRange Range("A1").CurrentRegion
      .Header = xlYes
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
    End With
End Sub

 

[lvizcarra]

Thanks, Fluff. I copied and pasted your code and still got the same runt-time error 1004.

 

[Michael M]

Is it possible that there is one of the sort fileds that is empty ??
That will give you the error !

 

[lvizcarra]

There is a column that occasionally has blank cells (column C). I commented out that line, but still go the error.

 

[Logit]

See if this accomplishes your goal :

Option Explicit


Sub DynamicRange()


ActiveSheet.UsedRange.Sort Key1:=Columns("A"), Order1:=xlAscending, _
      Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
      Orientation:=xlTopToBottom
  
End Sub


Sub foo()
Rows("2:2").Delete Shift:=xlUp
    Rows("1:1").Font.Bold = True
    Columns("A:A").EntireColumn.AutoFit
    Columns("B:B").EntireColumn.AutoFit
    Columns("C:C").EntireColumn.AutoFit
    Columns("D:D").EntireColumn.AutoFit
 
    DynamicRange
    
End Sub

 

[Michael M]

Sheet not protected is it ?
Have you stepped through the code using F8, to see which line gives the error ?
If there are no special characters in any of the columns, I'd suggest you upload the sheet to dropbox, so we can take a look !

 

[Fluff]

Do you have any merged cells?
Also what is the exact error message?