Sorting of an Array

[quej2003]

Hi All,

So I have an array, with a simplified look like so:

a b c d e
a b c d
a b c d e f


I was wondering if it is possible to basically "snap" everything to the right, so that the further right hand columns would have (decsendcing) e d f, second from right d c e, etc.

 

[Weaver]

So you have a range several columns in width where you need to 'pad' the cells to the left in order to make the right hand columns flush?

Or is it the same, but applied to a vb 2 dimensional array?

 

[quej2003]

So you have a range several columns in width where you need to 'pad' the cells to the left in order to make the right hand columns flush?

Or is it the same, but applied to a vb 2 dimensional array?

Hmm I guess I wasn't very clear, and I confess to not quite understanding your language/terminology, so will try again!

I have a medium sized data set that is normally about 100-250 rows going downwards, then a variable number of columns. Essentially I have imported a text file then used the delimiter (using space), but all the information I am looking to use is in the final 7 columns of each row. Hm does this help?

 

[GTO]

If the before/after is to be:

Excel Workbook
	A	B	C	D	E	F
1	a	b	c	d	e
2	a	b	c	d
3	a	b	c	d	e	f
4
5		a	b	c	d	e
6			a	b	c	d
7	a	b	c	d	e	f
Sheet4

Excel 2003

Maybe:

Sub exa2()
Dim ary
Dim x As Long, y As Long, i As Long
    
    ary = Range("A1:F3")
    
    For x = UBound(ary, 1) To LBound(ary, 1) Step -1
        For y = UBound(ary, 2) To LBound(ary, 2) Step -1
            If ary(x, y) = Empty And Not y = LBound(ary, 2) Then
                i = y
                Do While Not i< LBound(ary, 2)
                    If ary(x, i) = Empty Then
                        i = i - 1
                    Else
                        ary(x, y) = ary(x, i)
                        ary(x, i) = Empty
                        Exit Do
                    End If
                Loop
            End If
        Next
    Next
    Range("A1:F3").Offset(4) = ary
    
End Sub

Probably a faster way, but is that in the right direction?

 

[quej2003]

Hi,
thanks, thats basically what I was looking for yes! seems to work perfectly for myself, much obliged (+ apologises for any misunderstanding!)
Cheers

 

[Weaver]

that's what I thought you meant. glad you have your solution

 

[GTO]

Cat-killin' curiousity has the best of me I am afraid. In response to Weaver's question, you said that you are only using the last seven columns (after padding right). What's happening with the rest of them, are you manually/thru code just deleting the columns?

 

[quej2003]

Cat-killin' curiousity has the best of me I am afraid. In response to Weaver's question, you said that you are only using the last seven columns (after padding right). What's happening with the rest of them, are you manually/thru code just deleting the columns?

In a few words, am deleting yes! chop chop