- Sub 分户()
- '将原数据的列顺序做了调整,身份证放在姓名后面,方便数据处理
- Dim aArr, bArr(), tArr(1 To 6)
- aArr = Sheets("原表").[a1].CurrentRegion
- ReDim bArr(1 To 86, 1 To 1)
- For i = 2 To UBound(aArr, 1)
- If aArr(i, 1) = "户主" Then '表头
- n = n + 1
- m = 6
- ReDim Preserve bArr(1 To 86, 1 To n)
- bArr(1, n) = aArr(i, 2) '姓名
- bArr(2, n) = aArr(i, 4) '性别
- bArr(3, n) = aArr(i, 5) '年龄
- bArr(4, n) = aArr(i, 3) '身份证
- bArr(5, n) = aArr(i, 6) '地址
- bArr(6, n) = aArr(i, 7) '电话
- End If
- If m = 86 Then '复制表头
- n = n + 1
- m = 6
- ReDim Preserve bArr(1 To 86, 1 To n)
- For j = 1 To 6
- bArr(j, n) = bArr(j, n - 1)
- Next
- End If
- For j = 2 To 12
- If j <> 7 Then
- m = m + 1
- bArr(m, n) = aArr(i, j)
- End If
- Next
- Next
- Sheets("想变成这样的表").[a2].Resize(UBound(bArr, 2), UBound(bArr, 1)) = WorksheetFunction.Transpose(bArr) '输出到"想变成这样的表",其他位置可自行指定
- End Sub
复制代码
不问了,猜一个,原表的顺序要调整一下,身份证列移动到姓名列后面 |