|
本帖最后由 327718098 于 2017-5-22 13:24 编辑
Sub text1()
s = Time
Application.ScreenUpdating = False
Sheet2.Range("A" & Cells(Rows.Count, 1).End(3).Row + 1).Value = Sheets(6).Range("a1").Value
Sheet2.Range("b" & Cells(Rows.Count, 1).End(3).Row).Value = Sheets(6).Range("g1").Value
Dim xh As Byte, rng As Range, rng1 As Range
Worksheets(6).Range("A:A").Find("工段").Resize(1, 23).Copy
Sheet2.Range("c" & Cells(Rows.Count, 1).End(3).Row + 1).Offset(-1, 0).PasteSpecial Paste:=xlPasteValues
For xh = 6 To Worksheets.Count Step 1
Worksheets(xh).Range(Worksheets(xh).Range("a:A").Find("工段").Offset(1, 0), "w" & Worksheets(xh).Cells(Rows.Count, 1).End(3).EntireRow.Row).Copy
Sheet2.Range("c" & Cells(Rows.Count, 3).End(3).Row + 1).PasteSpecial Paste:=xlPasteValues
Sheet2.Range("b" & Cells(Rows.Count, 3).End(3).Row) = Worksheets(xh).Range("h1").Value
Sheet2.Range("a" & Cells(Rows.Count, 3).End(3).Row) = Worksheets(xh).Range("c1").Value
Next
For Each rng In Sheet2.Range("d4", Sheet2.Range("d1000000").End(xlUp))
rng.Offset(0, -2).Value = IIf(rng.Offset(0, -2).Value = "", rng.Offset(0, -2).End(xlDown).Value, rng.Offset(0, -2).Value)
rng.Offset(0, -3).Value = IIf(rng.Offset(0, -3).Value = "", rng.Offset(0, -3).End(xlDown).Value, rng.Offset(0, -3).Value)
If rng.Offset(0, -1).Value = "" Then
rng.Offset(0, -1).Value = rng.Offset(0, -1).End(3).Value
End If
If rng.Value = "部件" Or rng.Value = "" Then
If rng1 Is Nothing Then
Set rng1 = rng.EntireRow
Else
Set rng1 = Union(rng1, rng.EntireRow)
End If
End If
Next
rng1.Delete
Set rng1 = Nothing
For Each rng In Sheet2.Range("A3:w3")
If rng.Value = "" Then
If rng1 Is Nothing Then
Set rng1 = rng.EntireColumn
Else
Set rng1 = Union(rng1, rng.EntireColumn)
End If
End If
Next
Set rng1 = Union(rng1, Rows(Cells(Rows.Count, "d").End(3).Row + 1 & ":" & Cells(Rows.Count, "d").End(3).Row + 3).Select)
rng1.Delete
Application.ScreenUpdating = True
MsgBox "本次运行耗时" & Format(Time - s, "hh小时mm分ss秒")
End Sub
|
|