How do I rearrange columns based on cell value and not headers?
Results 1 to 2 of 2

Thread: How do I rearrange columns based on cell value and not headers?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2018
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default How do I rearrange columns based on cell value and not headers?

    Hi everyone,

    Is there a way to rearrange the columns based on values in the cell and not based on the headers?

    for example the first rearrange would be Username, Hint, Password, Site, the second rearrange would be Password, Username, Site, Hint.

    If i'm not being clear enough, please let me know and i'll try and be more descriptive.

    1 3 2 4
    2 1 4 3
    4 1 3 2
    Username Password Hint Site

  2. #2
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,834
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    12 Thread(s)

    Default Re: How do I rearrange columns based on cell value and not headers?

    With your data in "A1:D4" then
    Place this code in a Button as per code.
    Each time the code runs the data will be shown starting "F1", With columns rearranged as per rows.
    Code:
    Private Sub CommandButton3_Click()
    Dim Ray As Variant, Dn As Range, n As Long, K, num As Long, Ac As Long
    Ray = Range("A1:D4")
    Static c As Long
     c = c + 1
     c = IIf(c = UBound(Ray, 1), 1, c)
     With CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
        For Ac = 1 To UBound(Ray, 2)
            If Not .Exists(Ray(c, Ac)) Then
                .Add Ray(c, Ac), Ac
            End If
        Next
    
    ReDim nRay(1 To 4)
        For Each K In .keys
            n = n + 1
            num = Application.Small(.keys(), n)
            nRay(n) = .Item(num)
        Next K
    
    Range("F1").Resize(4, 4).Value = Application.Index(Ray, Evaluate("Row(1:" & UBound(Ray, 1) & ")"), nRay)
    End With
    
    End Sub
    Regards Mick
    Last edited by MickG; Apr 18th, 2019 at 01:18 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •