|
发表于 2017-2-6 16:37
|
显示全部楼层
本楼为最佳答案
- Option Explicit
- Sub 数据转移()
- Dim arr, result1, result2, result3, result4
- Dim k As Long
- Dim x As Long
- Dim i As Long
- Dim cnt As Long
- Dim temp As String
- Dim wb As Workbook
- Application.ScreenUpdating = False
- Set wb = Workbooks.Open("C:\Users\lsf800\Desktop" & "登记表")
- k = Cells(Rows.Count, 3).End(xlUp).Row
- arr = wb.Sheets("sheet1").Range("b3:d" & k).Value
- wb.Close 0
- ReDim result1(1 To UBound(arr), 1 To 1)
- ReDim result2(1 To UBound(arr), 1 To 1)
- ReDim result3(1 To UBound(arr), 1 To 1)
- ReDim result4(1 To UBound(arr), 1 To 1)
- For x = 2 To UBound(arr)
- If arr(x, 2) <> "" Then
- cnt = cnt + 1
- If arr(x, 1) <> "" Then
- result1(cnt, 1) = arr(x, 1)
- temp = arr(x, 3)
- Else:
- result1(cnt, 1) = result1(cnt - 1, 1)
- End If
- result2(cnt, 1) = "'" & temp
- result3(cnt, 1) = arr(x, 2)
- result4(cnt, 1) = "'" & arr(x, 3)
- End If
- Next x
- Workbooks("汇总表").Sheets("sheet1").Range("i2").Resize(UBound(arr), 1) = result1
- Workbooks("汇总表").Sheets("sheet1").Range("k2").Resize(UBound(arr), 1) = result2
- Workbooks("汇总表").Sheets("sheet1").Range("m2").Resize(UBound(arr), 1) = result3
- Workbooks("汇总表").Sheets("sheet1").Range("u2").Resize(UBound(arr), 1) = result4
- Application.ScreenUpdating = True
- End Sub
复制代码
这是删除空白行后的代码。
代码运行出现错误,中间有一段是调用登记表的路径,就是Set wb = Workbooks.Open("C:\Users\lsf800\Desktop\" & "登记表")这一段,文件保存的路径你要自己手动改一下的,亲~ |
|