Excel VBA以一定长度将列拆分为多行 [英] Excel VBA Split Column at Certain Length into Multiple Rows

问题描述

我有一张纸，上面有A:H列.我需要每60个字符将H列拆分为一个新行，并将A:G复制到这些新行中.

I have a sheet with columns from A:H. I need to split column H every 60 characters into a new row and copy A:G into these new rows.

这是数据的方式以及我想要的方式:

Here's how the data is and how I want it:

在一个理想的世界中，一开始会有一列，该列将对每条消息的每一行进行计数.因此，列 A1 为 1 ， A2 为 2 ， A3 为 1 ， A4 将是 2 ，依此类推.

In a perfect world there would be a column at the start that would count each row for each message. So column A1 would be 1, A2 would be 2, A3 would be 1, A4 would be 2, etc.

我需要执行29,453行，因此手动执行此操作并不是一种选择.

There are 29,453 rows that I need to do this to, so manually doing this is not really an option.

有什么帮助吗?

推荐答案

这并不那么困难.

首先，将所有日志记录存储在一个数组中

First, store all the log note in an array

第二，循环遍历数组中的每个日志记录，并为每个循环执行一次while循环以插入新行；将值复制到新行；每60个字符分割一次字符串.

Second, loop through every log note in the array and for every loop, do a do while loop to insert new row; copy values to new row; splitting the string every 60 characters.

只要子字符串的长度超过60，就继续循环.

Continue to loop as long as the length of the substring is more than 60.

第三，用于拆分H列的单元格；这可以通过使用Mid Function根据字符位置显示值来完成.

Third, for the spliting of the cell for column H; this can be done by using the Mid Function to display the values based on characer position.

例如，我们将从字符位置1到60显示第一行

For example, first row we will display from character position 1 to 60

例如 Mid(subString，1，60)

第二行，我们将从字符位置61开始显示

Whereas for second row we will display from character position 61 onwards

例如 Mid(subString，61，Len(subString))

然后在随后的do while循环中，第二行将从字符位置1到60等显示.

Then for subsequent do while loops, the second row will display from character position 1 to 60 etc.

有关中级功能的信息:

中间(字符串，开始，长度)

Mid(string, start, length)

字符串=原始全文

start =字符的起始位置

start = start position of character

length =字符长度

length = length of character

有关单元偏移的信息:

.Cells(rowNo，colNo).Offset(RowOffset，ColumnOffset)

.Cells(rowNo, colNo).Offset(RowOffset, ColumnOffset)

RowOffset =例如[1 =单元格下方1行] [-1 =单元格上方1行]

RowOffset = eg. [1 = 1 row below cell] [-1 = 1 row above cell]

ColumnOffset =例如[-1 =单元格右侧的col] [-1 = 1单元格左侧的col]

ColumnOffset = eg. [1 = 1 col to the right of cell] [-1 = 1 col to the left of cell]

完整解决方案

Option Explicit

'split every 60 characters new row
Sub SplitEverySixtyChar()

    Dim ws As Worksheet
    Dim rowNo As Long
    Dim lastRowNo As Long
    Dim arrayLogNote As Variant
    Dim logNote As Variant
    Dim subString As String


    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
    
        'Find last row no
        lastRowNo = (.Cells(.Rows.Count, 1).End(xlUp).Row)
    
        'Store all log note in array
        arrayLogNote = .Range("H2:H" & lastRowNo).Value
    
        'Starting row no is 2
        rowNo = 2
    
        For Each logNote In arrayLogNote
    
            subString = CStr(logNote)
        
            Do While Len(subString) > 60
        
                'Insert new row
                .Cells(rowNo, 1).Offset(1, 0).EntireRow.Insert
            
                'Copy the cell from A:G col into new row
                .Cells(rowNo, 1).Offset(1, 0).Value = .Cells(rowNo, 1).Value
                .Cells(rowNo, 2).Offset(1, 0).Value = .Cells(rowNo, 2).Value
                .Cells(rowNo, 3).Offset(1, 0).Value = .Cells(rowNo, 3).Value
                .Cells(rowNo, 4).Offset(1, 0).Value = .Cells(rowNo, 4).Value
                .Cells(rowNo, 5).Offset(1, 0).Value = .Cells(rowNo, 5).Value
                .Cells(rowNo, 6).Offset(1, 0).Value = .Cells(rowNo, 6).Value
                .Cells(rowNo, 7).Offset(1, 0).Value = .Cells(rowNo, 7).Value
            
                'Display text for new row from character position 60 onwards
                .Cells(rowNo, 8).Offset(1, 0).Value = Mid(subString, 61, Len(subString))
            
                'Display text from character position 1 to 60
                .Cells(rowNo, 8).Value = Mid(subString, 1, 60)
            
                subString = .Cells(rowNo, 8).Offset(1, 0).Value
            
                'Increment Row No
                rowNo = rowNo + 1
            
            Loop
                'Increment Row No
                rowNo = rowNo + 1
        Next logNote
   
    End With

End Sub
   

注意:这可能不是最有效的解决方案.肯定有比这更好的解决方案.

Note: This is probably not the most efficient solution out there. There are definitely better solution than this.

这篇关于Excel VBA以一定长度将列拆分为多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！