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

[Knockoutpie]

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.

[TABLE="width: 288"]
<tbody>[TR]
[TD="class: xl63, width: 72, align: right"]1[/TD]
[TD="class: xl63, width: 72, align: right"]3[/TD]
[TD="class: xl63, width: 72, align: right"]2[/TD]
[TD="class: xl63, width: 72, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]4[/TD]
[TD="class: xl63, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]4[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl64"]Username[/TD]
[TD="class: xl64"]Password[/TD]
[TD="class: xl64"]Hint[/TD]
[TD="class: xl64"]Site[/TD]
[/TR]
</tbody>[/TABLE]

 

[MickG]

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.

Private [COLOR="Navy"]Sub[/COLOR] CommandButton3_Click()
[COLOR="Navy"]Dim[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] K, num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Ray = Range("A1:D4")
Static c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
 c = c + 1
 c = IIf(c = UBound(Ray, 1), 1, c)
 [COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
    [COLOR="Navy"]For[/COLOR] Ac = 1 To UBound(Ray, 2)
        [COLOR="Navy"]If[/COLOR] Not .Exists(Ray(c, Ac)) [COLOR="Navy"]Then[/COLOR]
            .Add Ray(c, Ac), Ac
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR]

ReDim nRay(1 To 4)
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
        n = n + 1
        num = Application.Small(.keys(), n)
        nRay(n) = .Item(num)
    [COLOR="Navy"]Next[/COLOR] K

Range("F1").Resize(4, 4).Value = Application.Index(Ray, Evaluate("Row(1:" & UBound(Ray, 1) & ")"), nRay)
[COLOR="Navy"]End[/COLOR] With

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick