excel根据单元格值将单行拆分为多行 [英] Split single row into multiple rows based on cell value in excel

问题描述

我在 excel 中有一行数据.数据大约有 5000 多个值.

I have a single row of data in excel. Data is around 5000+ values.

我想把这一行分成多行.下面是同样的例子.

I want to split this single row into multiple rows.Below is the example of same.

我的单行包含如下数据，1 2 3 4 5 A 1 2 4 5 9 5 9 A 2 1 4 A 等...

My Single row contains data as follows, 1 2 3 4 5 A 1 2 4 5 9 5 9 A 2 1 4 A etc...

我希望在它达到每个A"值后拆分这一行.输出如下.

I want this single row to be split after every "A" value it reaches. Output below.

1 2 3 4 5

A 1 2 4 5 9 5 9

A 1 2 4 5 9 5 9

A 2 1 4

等等……

some1 可以帮助我如何做到这一点吗?宏对我来说很好.此外，我还有大量数据，例如 5000 多个值.

Can some1 help me as how this can be done? Macro is fine with me. Also I have huge data like 5000+ Values.

推荐答案

这应该可以解决您的问题.考虑到您的数据在 Sheet1 中，而输出是在 Worksheet Sheet2 中生成的.

This should do your job. Considering your data to be in Sheet1 and output is generated in Worksheet Sheet2.

 Sub pSplitData()

        Dim rngColLoop          As Range
        Dim rngSheet1           As Range
        Dim wksSheet2           As Worksheet
        Dim intColCounter       As Integer
        Dim intRowCounter       As Integer

        'Consider the data to be in First row of Sheet1
        With Worksheets("Sheet1")
            Set rngSheet1 = .Range(.Range("A1"), .Range("A1").End(xlToRight))
        End With

        Set wksSheet2 = Worksheets("Sheet2")
        intRowCounter = 1
        intColCounter = 0

        'Clear previous output
        wksSheet2.Range("A1").CurrentRegion.Clear

        'Loop through and create output in Sheet2
        With rngSheet1
            For Each rngColLoop In .Columns
                If Trim(rngColLoop) <> "" Then
                    If UCase(Trim(rngColLoop)) <> "A" Then
                        intColCounter = intColCounter + 1
                        wksSheet2.Cells(intRowCounter, intColCounter) = rngColLoop
                    ElseIf UCase(Trim(rngColLoop)) = "A" Then
                        intRowCounter = intRowCounter + 1
                        intColCounter = 1
                        wksSheet2.Cells(intRowCounter, intColCounter) = rngColLoop
                    End If
                End If
            Next rngColLoop
        End With

        Set rngColLoop = Nothing
        Set rngSheet1 = Nothing
        Set wksSheet2 = Nothing

End Sub

这篇关于excel根据单元格值将单行拆分为多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！