Excel：按行内容排列列 [英] Excel: Sort columns by row content

问题描述

这很难描述。

我有一些列，比如说三：

I have some columns, say three:

我想要得到的是：

其中XX是一个空单元格。

where XX is an empty cell.

有没有办法得到这个？

再见，
Thomas

Bye, Thomas

推荐答案

您可以使用ADO做很多。 >

You can do a lot with ADO.

Dim cn As Object
Dim rs As Object
Dim strFile As String
Dim strCon As String
Dim strSQL As String
Dim s As String
Dim i As Integer, j As Integer

''This is not the best way to refer to the workbook
''you want, but it is very convenient for notes
''It is probably best to use the name of the workbook.

strFile = ActiveWorkbook.FullName

''Note that if HDR=No, F1,F2 etc are used for column names,
''if HDR=Yes, the names in the first row of the range
''can be used.
''This is the Jet 4 connection string, you can get more
''here : http://www.connectionstrings.com/excel

strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
    & ";Extended Properties=""Excel 8.0;HDR=No;IMEX=1"";"

''Late binding, so no reference is needed

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon

strSQL = "SELECT 1 As Col, F1 As Cont FROM [Sheet1$] " _
       & "UNION ALL SELECT 2 As Col, F2 As Cont FROM [Sheet1$] " _
       & "UNION ALL SELECT 3 As Col, F3 As Cont FROM [Sheet1$] " _
       & "ORDER BY Cont"

rs.Open strSQL, cn, 3, 3

''Pick a suitable empty worksheet for the results

With Worksheets("Sheet2")

    ''Working with the recordset ...
    Do While Not rs.EOF
        If rs("Cont") > j Then i = i + 1

        j = rs("Cont")

        .Cells(i, rs("Col")) = rs("Cont")

       rs.MoveNext
    Loop
End With

这篇关于Excel：按行内容排列列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！