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!

 

[lvizcarra]

Yes, I stepped through it using F8 and there are no errors until I get to .Apply. That said, I have the worksheet open next to the VBE window and nothing happens to the worksheet at each step (i.e., the code doesn't result in any changes to the worksheet). In regards to special characters, the worksheet does show forward slashes and hyphens (/-). I will upload a copy of the sheet to dropbox and reply with the link.

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 !

 

[lvizcarra]

No merged cells. The exact error message is:

Run-time error '1004':

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.

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

 

[lvizcarra]

Using both routines works, but the worksheet is only sorted by column A. If that's the end result, I can work with that, but was hoping I could sort by columns A, B, C, F.

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

 

[Logit]

.
You can try this adjustment. May not be the prettiest but it does work.

Sub DynamicRange()


With Sheet1
    Range("A:A").Sort Key1:=Columns("A"), Order1:=xlAscending, _
      Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
      Orientation:=xlTopToBottom
End With
      
With Sheet1
    Range("B:B").Sort Key1:=Columns("B"), Order1:=xlAscending, _
      Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
      Orientation:=xlTopToBottom
End With
      
With Sheet1
    Range("C:C").Sort Key1:=Columns("C"), Order1:=xlAscending, _
      Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
      Orientation:=xlTopToBottom
End With
      
With Sheet1
    Range("F:F").Sort Key1:=Columns("F"), Order1:=xlAscending, _
      Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
      Orientation:=xlTopToBottom
End With


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

 

[lvizcarra]

Success! It works. Thank you, Logit. And thanks to everyone else for your input. Happy New Year everyone.

.
You can try this adjustment. May not be the prettiest but it does work.

Sub DynamicRange()


With Sheet1
    Range("A:A").Sort Key1:=Columns("A"), Order1:=xlAscending, _
      Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
      Orientation:=xlTopToBottom
End With
      
With Sheet1
    Range("B:B").Sort Key1:=Columns("B"), Order1:=xlAscending, _
      Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
      Orientation:=xlTopToBottom
End With
      
With Sheet1
    Range("C:C").Sort Key1:=Columns("C"), Order1:=xlAscending, _
      Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
      Orientation:=xlTopToBottom
End With
      
With Sheet1
    Range("F:F").Sort Key1:=Columns("F"), Order1:=xlAscending, _
      Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
      Orientation:=xlTopToBottom
End With


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

 

[MARK858]

Should there not be a lot of periods/full stops to link the ranges/columns to the With statements in the DynamicRange sub

Sub DynamicRange()


With Sheet1
    [COLOR="#FF0000"].[/COLOR]Range("A:A").Sort Key1:=[COLOR="#FF0000"].[/COLOR]Columns("A"), Order1:=xlAscending, _
      Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
      Orientation:=xlTopToBottom
End With
      
With Sheet1
    [COLOR="#FF0000"].[/COLOR]Range("B:B").Sort Key1:=[COLOR="#FF0000"].[/COLOR]Columns("B"), Order1:=xlAscending, _
      Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
      Orientation:=xlTopToBottom
End With
      
With Sheet1
    [COLOR="#FF0000"].[/COLOR]Range("C:C").Sort Key1:=[COLOR="#FF0000"].[/COLOR]Columns("C"), Order1:=xlAscending, _
      Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
      Orientation:=xlTopToBottom
End With
      
With Sheet1
    [COLOR="#FF0000"].[/COLOR]Range("F:F").Sort Key1:=[COLOR="#FF0000"].[/COLOR]Columns("F"), Order1:=xlAscending, _
      Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
      Orientation:=xlTopToBottom
End With


End Sub

or

Sub DynamicRange()


With Sheet1
    [COLOR="#FF0000"].[/COLOR]Range("A:A").Sort Key1:=[COLOR="#FF0000"].[/COLOR]Columns("A"), Order1:=xlAscending, _
      Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
      Orientation:=xlTopToBottom

    [COLOR="#FF0000"].[/COLOR]Range("B:B").Sort Key1:=[COLOR="#FF0000"].[/COLOR]Columns("B"), Order1:=xlAscending, _
      Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
      Orientation:=xlTopToBottom

    [COLOR="#FF0000"].[/COLOR]Range("C:C").Sort Key1:=[COLOR="#FF0000"].[/COLOR]Columns("C"), Order1:=xlAscending, _
      Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
      Orientation:=xlTopToBottom

    [COLOR="#FF0000"].[/COLOR]Range("F:F").Sort Key1:=[COLOR="#FF0000"].[/COLOR]Columns("F"), Order1:=xlAscending, _
      Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
      Orientation:=xlTopToBottom
End With


End Sub

 

[Logit]

.
lvizcarra

You are welcome.