多行和列到单列Excel [英] Multiple Rows and Columns to Single Column Excel

问题描述

我在Excel中有多个行和列的数据不同，我希望所有的数据都在一个列上。我尝试了转置函数，但并没有帮助我得到我想要的。

这是我现在所拥有的：

有人可以告诉我如何实现吗？任何内置函数或宏将是有帮助的。谢谢。

解决方案

尝试此代码：

 code> Sub RangetoColumn（）
 Dim LastRow As Long，LastColumn As Long 
 Dim CurrentSheet As Worksheet，TargetSheet As Worksheet 
 Dim i As Long，j As Long，Count As Long 
 
设置CurrentSheet = ThisWorkbook.Worksheets（Sheet1）' - >将Sheet1更改为您的源表
设置TargetSheet = ThisWorkbook.Worksheets（Sheet2）' - >更改Sheet2到你的目标表
 LastRow = CurrentSheet.Cells（Rows.Count，A）。End（xlUp）.Row 
 
 Count = 1 
 For i = 1 To LastRow 
 LastColumn = CurrentSheet.Cells（i，Columns.Count）.End（xlToLeft）.Column 
对于j = 1 To LastColumn 
 TargetSheet.Range（A& Count）。 Value = CurrentSheet.Cells（i，j）.Value 
 Count = Count + 1 
 Next j 
 Next i 
 End Sub 
  

代码将读取范围从 Sheet1 并将在 Sheet2 中创建一个列。

I have different data on multiple rows and columns in Excel and I want all the data to be on a single Column. I tried the Transpose Function but it's not helping me to get what I want.

This is what I have right now:

And this is what I want to get:

Can anyone kindly tell me how I can achieve that? Any built in function or Macro will be helpful. Thanks.

解决方案

Try this code:

Sub RangetoColumn()
    Dim LastRow As Long, LastColumn As Long
    Dim CurrentSheet As Worksheet, TargetSheet As Worksheet
    Dim i As Long, j As Long, Count As Long

    Set CurrentSheet = ThisWorkbook.Worksheets("Sheet1")'-->change Sheet1 to your source sheet
    Set TargetSheet = ThisWorkbook.Worksheets("Sheet2")'-->change Sheet2 to your target sheet
    LastRow = CurrentSheet.Cells(Rows.Count, "A").End(xlUp).Row

    Count = 1
    For i = 1 To LastRow
        LastColumn = CurrentSheet.Cells(i, Columns.Count).End(xlToLeft).Column
        For j = 1 To LastColumn
            TargetSheet.Range("A" & Count).Value = CurrentSheet.Cells(i, j).Value
            Count = Count + 1
        Next j
    Next i
End Sub

Code will read Range from Sheet1 and will create a Column in Sheet2.

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