• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Re-arranging columns using Arrays

Dr. Demento

Dr. Demento

Member
p45cal came up with an awesome response to this question in 2015 (response #5).

However, I can't seem to get it to work; I thought it was working earlier but I believe had suppressed the error or hadn't noticed it. Below is the code I adapted from his.

At the line where With hdr.Resize(maxRow), I get RTE 424 - Object required. The TypeName(hdr) = String. I understand that Resize is a Range function, but I have no idea how to compensate for this error. I've tried assigning hdr as Range but that threw another error.

If anyone could give me some pointers, I'd be grateful! I'm not even sure what the Resize line does. :confused:

Thanks y'all.

Code: 
Sub run_sort_Columns_arr()

Dim sht As Worksheet
  Set sht = ActiveSheet
 
Dim arr As Variant
  arr = Array("SSN", "LASTNAME", "FIRSTNAME", "    BIRTHDATE")
 
  sort_Columns_arr sht, arr

End Sub


Public Sub sort_Columns_arr(Optional ths As Worksheet, _
                            Optional rraOrder As Variant)
' ~~ Sorts columns using an array to direct order; _
      however, will not order columns not listed in array _
      ***** twice as FAST as range version *****
' http://forum.chandoo.org/threads/re-arranging-columns-basis-hdr-names-vba-codes-using-array.24537/

Dim wbk As Workbook
  Set wbk = ActiveWorkbook
If ths Is Nothing Then _
  Set ths = wbk.ActiveworkSheet

Dim rngUsed As Range, _
    rngHdr As Range, _
    rngDest As Range, _
    rng As Range, _
    cell As Range
  Set rngUsed = ths.UsedRange
  Set rngHdr = rngUsed.Rows(1)
 
Dim cntr As Long, _
    colArr As Long, _
    rowArr As Long, _
    cntrRow As Long, _
    maxRow As Long, _
    colArr_max As Long
  maxRow = rngUsed.Rows.Count
  colArr = 0
  colArr_max = 0
 
Dim str_shtName As String
 
Dim arrResults As Variant, _
    hdr As Variant, _
    arrHdr As Variant, _
    arr As Variant


  ReDim arrHdr(1 To UBound(rraOrder))
 
  ' ~~ Remove all spaces in header terms & change all formatting to General
  With ths
    rngUsed.NumberFormat = "General"
    For Each cell In rngHdr.Cells
      cell.value = UCase(cell.value)
      cell.value = str_trimAll(cell.value)
    Next cell
  End With
 
  For Each hdr In rraOrder
    Set rng = rngHdr.find(What:=hdr, _
                          LookAt:=xlWhole, _
                          LookIn:=xlValues, _
                          MatchCase:=False, _
                          searchformat:=False)
   
    ' ~~ If rng is cannot be found in rngUsed, skip to next entry in rraOrder
    If Not rng Is Nothing Then
      colArr_max = colArr_max + 1
      arrHdr(colArr_max) = rng ' adds the hdr cell to the array for later use
'      Debug.Print "arrHdr(" & colArr_max & ") | " & arrHdr(colArr_max)
    End If
  Next hdr
 
  ' ~~ To ReDim array to proper size, must only use header variables found in rngHdr
  ReDim Preserve arrHdr(1 To colArr_max)
  ReDim arrResults(1 To maxRow, 1 To colArr_max) ' ~~ ReDim the final size of the array

  For Each hdr In arrHdr 'takes each of the hdr cells in turn
    colArr = colArr + 1
    With hdr.Resize(maxRow)  'expands the range
      arr = .value 'puts the values into memory (arr)
      .ClearContents 'clears that column's contents; the values are stored in memory.
    End With

    For rowArr = 1 To maxRow  ' ~~ importing each cell of the column into array
      arrResults(rowArr, colArr) = arr(rowArr, 1) ' and put it into the final result array
    Next rowArr

  Next hdr

  With ths
    .Select
    rngUsed.Cells(1, 1).Resize(maxRow, colArr) = arrResults ' ~~ Copy final array onto the destination sheet
'    rngUsed.Range(Cells(, colArr_max + 1), Cells(, rngUsed.Columns.count)).EntireColumn.Delete  ' ~~ Delete "extra" data outside specified array
  End With
 
End Sub
 
Looks like you are missing some Subs.

"str_trimAll" for one.

Also hdr is string and not range variable at that line in code.

Looking at the code hdr is declared as variant and used in ".Find" as string.

So when you get to "With hdr" line, it is holding string value and can't be used as range variable.

Personally, I'd not use array in this manner. What is it exactly you are trying to accomplish here?

Edit: ".Resize" is property for range that will resize specified range to desired size. Range("A1:A12").Resize(1) will shrink range to Range("A1"). Range("A1:A12").Resize(1, 2) will change range dimension to 1 row & 2 columns. IE. Range("A1:B1") etc.
 
Last edited:
I wonder whether the sub str_trimAll is just the same as Application.Trim (=Application.worksheetfunction.trim)?
 
You must log in or register to reply here.
Back
Top