title: VBA Excel sorting on multiple columns
body: <p>I have an Excel table, where I'd like that all rows (except the header row) in Sheet "CR" that have a value in it (excluding formulas if possible (column A contains formulas)) are sorted first by column B (name = TEAM), then C (name = BUILDING) and finally D (name = DATE_MAJ) before the file is saved.</p>
  
  <p>I'm an absolute noob with VBA, so I'm trying out stuff that I find left and right on the fora and modify it to my needs. From searching around, I tried this code in the Excel VBA Object 'Workbook', but it gives an error:</p>
  
  <pre><code>Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  'Setup column names
  Col1name = "SECTION"
  Col2name = "BATIMENT"
  Col3name = "DATE_MAJ"
  
  'Find cols
  For Each cell In Range("A1:" &amp; Range("A1").End(xlToRight).Address)
      If cell.Value = Col1name Then
          Col1 = cell.Column
      End If
      If cell.Value = Col2name Then
          Col2 = cell.Column
      End If
      If cell.Value = Col3name Then
          Col3 = cell.Column
      End If
  
  Next
  
  'Below two line:- if they are blank e.g. column not found it will error so a small bit of error handling 
  If Col1 = "" Then Exit Sub
  If Col2 = "" Then Exit Sub
  If Col3 = "" Then Exit Sub
  
  'Find last row - dynamic part
  lastrow = ActiveSheet.Range("A100000").End(xlUp).Row
  
  'Convert col numer to name
  Col1 = Split(Cells(1, Col1).Address(True, False), "$")
  Col2 = Split(Cells(1, Col2).Address(True, False), "$")
  Col3 = Split(Cells(1, Col3).Address(True, False), "$")
  
  'Sort
  With ActiveSheet.Sort
      .SortFields.Clear
      .SortFields.Add Key:=Range(Col1(0) &amp; "2:" &amp; Col1(0) &amp; lastrow) _
          , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
      .SortFields.Add Key:=Range(Col2(0) &amp; "2:" &amp; Col2(0) &amp; lastrow) _
          , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
      .SortFields.Add Key:=Range(Col3(0) &amp; "2:" &amp; Col3(0) &amp; lastrow) _
          , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  
      .SetRange Range("A1:K" &amp; lastrow)
      .Header = xlYes
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
  End With
  End Sub
  </code></pre>
  
  <p>I'd be grateful for any help in getting the code right. Below is a link to the Excel file (I took out the above code as it didn't work). </p>
  
  <p><a href="https://www.dropbox.com/s/gx5y2onh3gg54d5/CR%20projet2-test.xlsm?dl=0" rel="nofollow noreferrer">Dropbox link to Excel file</a></p>