如何创建宏以拆分列和行?
问题描述:
我有一个长的工作表,其中包括3列:如何创建宏以拆分列和行?
柱A =发票号码
列B =接收量
列C - 支付金额
在列中的每个号码A需要分割到两行:付款和接收。
对于收费将采取准数从列C
对于接收将准数从塔B
答
我已经创建简单的VBA代码的行从工作表Sheet分成列向的Sheet 2中Activeworkbook。您可以根据需要修改代码。
Dim sel As Range, b As Range
Dim rcount As Integer: rcount = 1
ActiveWorkbook.Sheets(2).Range("A:C").Delete Shift:=xlDown
With ActiveWorkbook.Sheets(1)
Set sel = .Range("A2:C" & .UsedRange.Rows.Count)
For Each b In sel.Rows
ActiveWorkbook.Sheets(2).Cells(rcount, 1).Value = b.Value2(1, 1)
ActiveWorkbook.Sheets(2).Cells(rcount, 2).Value = .Cells(1, 2).Value
ActiveWorkbook.Sheets(2).Cells(rcount, 3).Value = b.Value2(1, 2)
ActiveWorkbook.Sheets(2).Cells(rcount + 1, 1).Value = b.Value2(1, 1)
ActiveWorkbook.Sheets(2).Cells(rcount + 1, 2).Value = .Cells(1, 3).Value
ActiveWorkbook.Sheets(2).Cells(rcount + 1, 3).Value = b.Value2(1, 3)
rcount = rcount + 2
Next
End With
答
假设你的数据在Sheet1
,我们将让Sheet2
的splited数据。这里有一个简单的解决方案:
Sub Split_Column()
Set st1 = ThisWorkbook.Sheets("Sheet1")
Set st2 = ThisWorkbook.Sheets("Sheet2")
j = 1
For i = 1 To st1.UsedRange.Rows.Count
st2.Cells(j, 1) = st1.Cells(i, 1)
st2.Cells(j, 2) = "Pay"
st2.Cells(j, 3) = st1.Cells(i, 3)
j = j + 1
st2.Cells(j, 1) = st1.Cells(i, 1)
st2.Cells(j, 2) = "Receive"
st2.Cells(j, 3) = st1.Cells(i, 2)
j = j + 1
Next
End Sub
如果你看看powerquery,你可以设置你的数据了一个表,那么这个表里面去powerquery或数据(2016)选项卡>从表查询,然后在出现的窗口选择最后2列并右键单击unpivot列 – QHarr
感谢您的回复。这个powerquery在哪里?我有Office 2013 – tasoik
2013它是一个免费的微软[加载项](https://www.microsoft.com/en-gb/download/details.aspx?id=39379) - 如果工作机器检查你是允许安装。 – QHarr