sorting in VBA

[G2K]

Hi All,

i want to sort selected data by row and by column respectively in excel VBA. i have more than 100 tables in my worksheet and i want to sort each tabledata by row, first, and then by column

sample of problme--

<TABLE dir=ltr borderColor=#05ad00 cellSpacing=1 cellPadding=2 width=448 border=1><TBODY><TR><TD width="14%" height=4>Table1

​

</TD><TD width="14%" height=4>ABC

​

</TD><TD width="14%" height=4>DEF

​

</TD><TD width="14%" height=4>GHI

​

</TD><TD width="14%" height=4>JKL

​

</TD><TD width="14%" height=4>MNO

​

</TD><TD width="14%" height=4>RST

​

</TD></TR><TR><TD width="14%" height=4>PQR

​

</TD><TD width="14%" height=4>1

​

</TD><TD width="14%" height=4>3

​

</TD><TD width="14%" height=4>5

​

</TD><TD width="14%" height=4>7

​

</TD><TD width="14%" height=4>9

​

</TD><TD width="14%" height=4>11

​

</TD></TR><TR><TD width="14%" height=4>STU

​

</TD><TD width="14%" height=4>2

​

</TD><TD width="14%" height=4>4

​

</TD><TD width="14%" height=4>6

​

</TD><TD width="14%" height=4>8

​

</TD><TD width="14%" height=4>10

​

</TD><TD width="14%" height=4>12

​

</TD></TR><TR><TD width="14%" height=4>MNO

​

</TD><TD width="14%" height=4>3

​

</TD><TD width="14%" height=4>5

​

</TD><TD width="14%" height=4>7

​

</TD><TD width="14%" height=4>9

​

</TD><TD width="14%" height=4>11

​

</TD><TD width="14%" height=4>13

​

</TD></TR><TR><TD width="14%" height=4>QRS

​

</TD><TD width="14%" height=4>4

​

</TD><TD width="14%" height=4>6

​

</TD><TD width="14%" height=4>8

​

</TD><TD width="14%" height=4>10

​

</TD><TD width="14%" height=4>12

​

</TD><TD width="14%" height=4>14

​

</TD></TR><TR><TD width="14%" height=4>GHI

​

</TD><TD width="14%" height=4>5

​

</TD><TD width="14%" height=4>7

​

</TD><TD width="14%" height=4>9

​

</TD><TD width="14%" height=4>11

​

</TD><TD width="14%" height=4>13

​

</TD><TD width="14%" height=4>15

</TD></TR></TBODY></TABLE>​

Desired output--

<TABLE dir=ltr borderColor=#0ead00 cellSpacing=1 cellPadding=2 width=448 border=1><TBODY><TR><TD width="14%" height=4>Table1

​

</TD><TD width="14%" height=4>ABC

​

</TD><TD width="14%" height=4>DEF

​

</TD><TD width="14%" height=4>GHI

​

</TD><TD width="14%" height=4>JKL

​

</TD><TD width="14%" height=4>MNO

​

</TD><TD width="14%" height=4>RST

​

</TD></TR><TR><TD width="14%" height=4>GHI

​

</TD><TD width="14%" height=4>5

​

</TD><TD width="14%" height=4>7

​

</TD><TD width="14%" height=4>9

​

</TD><TD width="14%" height=4>11

​

</TD><TD width="14%" height=4>13

​

</TD><TD width="14%" height=4>15

​

</TD></TR><TR><TD width="14%" height=4>MNO

​

</TD><TD width="14%" height=4>3

​

</TD><TD width="14%" height=4>5

​

</TD><TD width="14%" height=4>7

​

</TD><TD width="14%" height=4>9

​

</TD><TD width="14%" height=4>11

​

</TD><TD width="14%" height=4>13

​

</TD></TR><TR><TD width="14%" height=4>PQR

​

</TD><TD width="14%" height=4>1

​

</TD><TD width="14%" height=4>3

​

</TD><TD width="14%" height=4>5

​

</TD><TD width="14%" height=4>7

​

</TD><TD width="14%" height=4>9

​

</TD><TD width="14%" height=4>11

​

</TD></TR><TR><TD width="14%" height=4>QRS

​

</TD><TD width="14%" height=4>4

​

</TD><TD width="14%" height=4>6

​

</TD><TD width="14%" height=4>8

​

</TD><TD width="14%" height=4>10

​

</TD><TD width="14%" height=4>12

​

</TD><TD width="14%" height=4>14

​

</TD></TR><TR><TD width="14%" height=4>STU

​

</TD><TD width="14%" height=4>2

​

</TD><TD width="14%" height=4>4

​

</TD><TD width="14%" height=4>6

​

</TD><TD width="14%" height=4>8

​

</TD><TD width="14%" height=4>10

​

</TD><TD width="14%" height=4>12

</TD></TR></TBODY></TABLE>​

just want to write a procedure to sort given range first row by Row and first column by column. pls help.....

thanks

 

[Andrew Poulsom]

You can get your desired output by sorting the entire sample data by column 1. Or is your real problem more complicated?

 

[G2K]

Thanks Andrew,

i think just sorting column one is not enough as i need data sorted by row also.if i sort column one, data will be sorted by column but i need to sort data by row as well

i know how to sort data by column but have no idea how to sort data by row in VBA

thanks

 

[snowblizz]

This gives exactly your desired output when I test.

Range("A2:G6").Sort Key1:=Range("A2"), Order1:=xlAscending

 

[G2K]

exactly, but it can not sort data by row i.e b1 to G1.

unfortunately, in given example data is in sorted order so it seems data is being sorted by given code. please change the column order and headers in zumbled order and try again.

Thnaks.

 

[Andrew Poulsom]

Please post a sample with unsorted rows and columns and the desired result.

 

[G2K]

OK,

sample-(Problem)

<TABLE dir=ltr borderColor=#10ad00 cellSpacing=1 cellPadding=2 width=538 border=1><TBODY><TR><TD width="12%" bgColor=#ff6600 height=16>Table

​

</TD><TD width="13%" height=16>London

​

</TD><TD width="12%" height=16>Southern

​

</TD><TD width="13%" height=16>Property

​

</TD><TD width="12%" height=16>Global

​

</TD><TD width="12%" height=16>Leveraged

​

</TD><TD width="13%" height=16>Northern

​

</TD><TD width="12%" height=16>Eastern

​

</TD></TR><TR><TD width="12%" height=16>Clean

​

</TD><TD width="13%" height=16>34

​

</TD><TD width="12%" height=16>62

​

</TD><TD width="13%" height=16>6

​

</TD><TD width="12%" height=16>1

​

</TD><TD width="12%" height=16>3

​

</TD><TD width="13%" height=16>25

​

</TD><TD width="12%" height=16>38

​

</TD></TR><TR><TD width="12%" height=16>EWL 2

​

</TD><TD width="13%" height=16>916

​

</TD><TD width="12%" height=16>1381

​

</TD><TD width="13%" height=16>240

​

</TD><TD width="12%" height=16>264

​

</TD><TD width="12%" height=16>96

​

</TD><TD width="13%" height=16>1430

​

</TD><TD width="12%" height=16>1301

​

</TD></TR><TR><TD width="12%" height=16>EWL 1

​

</TD><TD width="13%" height=16>588

​

</TD><TD width="12%" height=16>274

​

</TD><TD width="13%" height=16>150

​

</TD><TD width="12%" height=16>146

​

</TD><TD width="12%" height=16>95

​

</TD><TD width="13%" height=16>446

​

</TD><TD width="12%" height=16>359

​

</TD></TR><TR><TD width="12%" height=16>B&D

​

</TD><TD width="13%" height=16>141

​

</TD><TD width="12%" height=16>165

​

</TD><TD width="13%" height=16>13

​

</TD><TD width="12%" height=16>5

​

</TD><TD width="12%" height=16>9

​

</TD><TD width="13%" height=16>87

​

</TD><TD width="12%" height=16>112

​

</TD></TR><TR><TD width="12%" height=16>EWL 3

​

</TD><TD width="13%" height=16>82

​

</TD><TD width="12%" height=16>15

​

</TD><TD width="13%" height=16>11

​

</TD><TD width="12%" height=16>226

​

</TD><TD width="12%" height=16>17

​

</TD><TD width="13%" height=16>46

​

</TD><TD width="12%" height=16>28

​

</TD></TR></TBODY></TABLE>​

Sample (Desired Output)

<TABLE dir=ltr borderColor=#1aad00 cellSpacing=1 cellPadding=2 width=538 border=1><TBODY><TR><TD width="12%" bgColor=#ff6600 height=16>

Table​

​

​

​

​

​

</TD><TD width="13%" height=16>

Eastern​

​

​

​

​

</TD><TD width="12%" height=16>

Global​

​

​

​

​

</TD><TD width="13%" height=16>

Leveraged​

​

​

​

​

</TD><TD width="12%" height=16>

London​

​

​

​

​

</TD><TD width="12%" height=16>

Northern​

​

​

​

​

</TD><TD width="13%" height=16>

Property​

​

​

​

​

</TD><TD width="12%" height=16>

Southern​

​

​

​

​

</TD></TR><TR><TD width="12%" height=16>

B&D​

​

​

​

​

</TD><TD width="13%" height=16>

125​

​

​

​

​

</TD><TD width="12%" height=16>

4​

​

​

​

​

</TD><TD width="13%" height=16>

4​

​

​

​

​

</TD><TD width="12%" height=16>

156​

​

​

​

​

</TD><TD width="12%" height=16>

91​

​

​

​

​

</TD><TD width="13%" height=16>

11​

​

​

​

​

</TD><TD width="12%" height=16>

147​

​

​

​

​

</TD></TR><TR><TD width="12%" height=16>

Clean​

​

​

​

​

</TD><TD width="13%" height=16>

26​

​

​

​

​

</TD><TD width="12%" height=16>

6​

​

​

​

​

</TD><TD width="13%" height=16>

7​

​

​

​

​

</TD><TD width="12%" height=16>

51​

​

​

​

​

</TD><TD width="12%" height=16>

26​

​

​

​

​

</TD><TD width="13%" height=16>

0​

​

​

​

​

</TD><TD width="12%" height=16>

59​

​

​

​

​

</TD></TR><TR><TD width="12%" height=16>

EWL 1​

​

​

​

​

</TD><TD width="13%" height=16>

357​

​

​

​

​

</TD><TD width="12%" height=16>

104​

​

​

​

​

</TD><TD width="13%" height=16>

106​

​

​

​

​

</TD><TD width="12%" height=16>

521​

​

​

​

​

</TD><TD width="12%" height=16>

446​

​

​

​

​

</TD><TD width="13%" height=16>

143​

​

​

​

​

</TD><TD width="12%" height=16>

310​

​

​

​

​

</TD></TR><TR><TD width="12%" height=16>

EWL 2​

​

​

​

​

</TD><TD width="13%" height=16>

1321​

​

​

​

​

</TD><TD width="12%" height=16>

272​

​

​

​

​

</TD><TD width="13%" height=16>

85​

​

​

​

​

</TD><TD width="12%" height=16>

864​

​

​

​

​

</TD><TD width="12%" height=16>

1410​

​

​

​

​

</TD><TD width="13%" height=16>

183​

​

​

​

​

</TD><TD width="12%" height=16>

1333​

​

​

​

​

</TD></TR><TR><TD width="12%" height=16>

EWL 3​

​

​

​

​

</TD><TD width="13%" height=16>

25​

​

​

​

​

</TD><TD width="12%" height=16>

251​

​

​

​

​

</TD><TD width="13%" height=16>

20​

​

​

​

​

</TD><TD width="12%" height=16>

89​

​

​

​

​

</TD><TD width="12%" height=16>

48​

​

​

​

​

</TD><TD width="13%" height=16>

56​

​

​

​

​

</TD><TD width="12%" height=16>

24​

​

​

​

​

</TD></TR></TBODY></TABLE>​

please note: i have more than 100 tables to sort, so i want a code that can sort any table by just calling a procedure.

Thanks

 

[G2K]

hi Andrew,

sorry for ignoring the value order in previous post. below is the correct sample data..

Problem

<TABLE dir=ltr borderColor=#02ad00 cellSpacing=1 cellPadding=2 width=538 border=1><TBODY><TR><TD width="12%" bgColor=#ff6600 height=16>

Table​

​

​

​

​

​

</TD><TD width="13%" height=16>

London​

​

​

​

​

</TD><TD width="12%" height=16>

Southern​

​

​

​

​

</TD><TD width="13%" height=16>

Property​

​

​

​

​

</TD><TD width="12%" height=16>

Global​

​

​

​

​

</TD><TD width="12%" height=16>

Leveraged​

​

​

​

​

</TD><TD width="13%" height=16>

Northern​

​

​

​

​

</TD><TD width="12%" height=16>

Eastern​

​

​

​

​

</TD></TR><TR><TD width="12%" height=16>

EWL 3​

​

​

​

​

</TD><TD width="13%" height=16>

89​

​

​

​

​

</TD><TD width="12%" height=16>

24​

​

​

​

​

</TD><TD width="13%" height=16>

56​

​

​

​

​

</TD><TD width="12%" height=16>

251​

​

​

​

​

</TD><TD width="12%" height=16>

20​

​

​

​

​

</TD><TD width="13%" height=16>

48​

​

​

​

​

</TD><TD width="12%" height=16>

25​

​

​

​

​

</TD></TR><TR><TD width="12%" height=16>

EWL 2​

​

​

​

​

</TD><TD width="13%" height=16>

864​

​

​

​

​

</TD><TD width="12%" height=16>

1333​

​

​

​

​

</TD><TD width="13%" height=16>

183​

​

​

​

​

</TD><TD width="12%" height=16>

272​

​

​

​

​

</TD><TD width="12%" height=16>

85​

​

​

​

​

</TD><TD width="13%" height=16>

1410​

​

​

​

​

</TD><TD width="12%" height=16>

1321​

​

​

​

​

</TD></TR><TR><TD width="12%" height=16>

EWL 1​

​

​

​

​

</TD><TD width="13%" height=16>

521​

​

​

​

​

</TD><TD width="12%" height=16>

310​

​

​

​

​

</TD><TD width="13%" height=16>

143​

​

​

​

​

</TD><TD width="12%" height=16>

104​

​

​

​

​

</TD><TD width="12%" height=16>

106​

​

​

​

​

</TD><TD width="13%" height=16>

446​

​

​

​

​

</TD><TD width="12%" height=16>

357​

​

​

​

​

</TD></TR><TR><TD width="12%" height=16>

Clean​

​

​

​

​

</TD><TD width="13%" height=16>

51​

​

​

​

​

</TD><TD width="12%" height=16>

59​

​

​

​

​

</TD><TD width="13%" height=16>

0​

​

​

​

​

</TD><TD width="12%" height=16>

6​

​

​

​

​

</TD><TD width="12%" height=16>

7​

​

​

​

​

</TD><TD width="13%" height=16>

26​

​

​

​

​

</TD><TD width="12%" height=16>

26​

​

​

​

​

</TD></TR><TR><TD width="12%" height=16>

B&D​

​

​

​

​

</TD><TD width="13%" height=16>

156​

​

​

​

​

</TD><TD width="12%" height=16>

147​

​

​

​

​

</TD><TD width="13%" height=16>

11​

​

​

​

​

</TD><TD width="12%" height=16>

4​

​

​

​

​

</TD><TD width="12%" height=16>

4​

​

​

​

​

</TD><TD width="13%" height=16>

91​

​

​

​

​

</TD><TD width="12%" height=16>

125​

​

​

​

​

</TD></TR></TBODY></TABLE>


Expected Output

<TABLE dir=ltr borderColor=#0bad00 cellSpacing=1 cellPadding=2 width=538 border=1><TBODY><TR><TD width="12%" bgColor=#ff6600 height=16>

Table​

​

​

​

​

​

</TD><TD width="13%" height=16>

Eastern​

​

​

​

​

</TD><TD width="12%" height=16>

Global​

​

​

​

​

</TD><TD width="13%" height=16>

Leveraged cases​

​

​

​

​

</TD><TD width="12%" height=16>

London​

​

​

​

​

</TD><TD width="12%" height=16>

Northern​

​

​

​

​

</TD><TD width="13%" height=16>

Property​

​

​

​

​

</TD><TD width="12%" height=16>

Southern​

​

​

​

​

</TD></TR><TR><TD width="12%" height=16>

B&D​

​

​

​

​

</TD><TD width="13%" height=16>

125​

​

​

​

​

</TD><TD width="12%" height=16>

4​

​

​

​

​

</TD><TD width="13%" height=16>

4​

​

​

​

​

</TD><TD width="12%" height=16>

156​

​

​

​

​

</TD><TD width="12%" height=16>

91​

​

​

​

​

</TD><TD width="13%" height=16>

11​

​

​

​

​

</TD><TD width="12%" height=16>

147​

​

​

​

​

</TD></TR><TR><TD width="12%" height=16>

Clean​

​

​

​

​

</TD><TD width="13%" height=16>

26​

​

​

​

​

</TD><TD width="12%" height=16>

6​

​

​

​

​

</TD><TD width="13%" height=16>

7​

​

​

​

​

</TD><TD width="12%" height=16>

51​

​

​

​

​

</TD><TD width="12%" height=16>

26​

​

​

​

​

</TD><TD width="13%" height=16>

0​

​

​

​

​

</TD><TD width="12%" height=16>

59​

​

​

​

​

</TD></TR><TR><TD width="12%" height=16>

EWL 1​

​

​

​

​

</TD><TD width="13%" height=16>

357​

​

​

​

​

</TD><TD width="12%" height=16>

104​

​

​

​

​

</TD><TD width="13%" height=16>

106​

​

​

​

​

</TD><TD width="12%" height=16>

521​

​

​

​

​

</TD><TD width="12%" height=16>

446​

​

​

​

​

</TD><TD width="13%" height=16>

143​

​

​

​

​

</TD><TD width="12%" height=16>

310​

​

​

​

​

</TD></TR><TR><TD width="12%" height=16>

EWL 2​

​

​

​

​

</TD><TD width="13%" height=16>

1321​

​

​

​

​

</TD><TD width="12%" height=16>

272​

​

​

​

​

</TD><TD width="13%" height=16>

85​

​

​

​

​

</TD><TD width="12%" height=16>

864​

​

​

​

​

</TD><TD width="12%" height=16>

1410​

​

​

​

​

</TD><TD width="13%" height=16>

183​

​

​

​

​

</TD><TD width="12%" height=16>

1333​

​

​

​

​

</TD></TR><TR><TD width="12%" height=16>

EWL 3​

​

​

​

​

</TD><TD width="13%" height=16>

25​

​

​

​

​

</TD><TD width="12%" height=16>

251​

​

​

​

​

</TD><TD width="13%" height=16>

20​

​

​

​

​

</TD><TD width="12%" height=16>

89​

​

​

​

​

</TD><TD width="12%" height=16>

48​

​

​

​

​

</TD><TD width="13%" height=16>

56​

​

​

​

​

</TD><TD width="12%" height=16>

24​

​

​

​

​

</TD></TR></TBODY></TABLE>

thanks

 

[Andrew Poulsom]

If your data is in the range A1:H6, try:

Sub Test()
    Dim Sh As Worksheet
    Dim Rng As Range
    Dim i As Integer
    Dim j As Integer
    Set Sh = Worksheets("Sheet1")
    Set Rng = Sh.Range("A1").CurrentRegion
    With Rng
        .Offset(0, 1).Resize(, Rng.Columns.Count - 1).Sort _
            Key1:=.Cells(1, 2), Order1:=xlAscending, Header:=xlGuess, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight
        .Sort Key1:=.Cells(2, 1), Order1:=xlAscending, Header:=xlGuess, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    End With
End Sub

 

[G2K]

thanks Andrew,

It's working like a magic. thanks a ton....