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

问题描述

我在excel中有一行数据。数据是大约5000+以上的值。

我想将这个单行拆分成多行。以下是相同的例子。

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

我希望这个单独的行在每到达A值之后被分割。

1 2 3 4 5

A 1 2 4 5 9 5 9

A 2 1 4

A等...

有人可以帮助我吗？宏对我很好另外我有巨大的数据，如5000+值。

解决方案

这应该做你的工作。考虑到您的数据在Sheet1中，输出是在Worksheet Sheet2中生成的。

  Sub pSplitData（）
 
 Dim rngColLoop As Range 
 Dim rngSheet1 As Range 
 Dim wksSheet2 As Worksheet 
 Dim intColCounter As Integer 
 Dim intRowCounter As Integer 
 
'考虑数据到在Sheet1的第一行
使用工作表（Sheet1）
设置rngSheet1 = .Range（.Range（A1），.Range（A1）。End（xlToRight））
结束
 
设置wksSheet2 =工作表（Sheet2）
 intRowCounter = 1 
 intColCounter = 0 
 
'清除以前的输出
 wksSheet2.Range（A1）。CurrentRegion.Clear 
 
'循环并在Sheet2中创建输出
带rngSheet1 
每个rngColLoop在.Columns 
如果修剪（rngColLoop） 然后
如果UCase（Trim（rngColLoop））<> Athen 
 intColCounter = intColCounter + 1 
 wksSheet2.Cells（intRowCounter，intColCounter）= rngColLoop 
 ElseIf UCase（Trim（rngColLoop））=A然后
 intRowCounter = intRowCounter + 1 
 intColCounter = 1 
 wksSheet2.Cells（intRowCounter，intColCounter）= rngColLoop 
 End If 
 End If 
 Next rngColLoop 
 End With 
 
设置rngColLoop =没有
设置rngSheet1 =没有
设置wksSheet2 =没有
 
结束Sub 
  

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.

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...

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 2 1 4

A Etc...

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

解决方案

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屋！