|
楼主 |
发表于 2019-3-13 12:00
|
显示全部楼层
大神你能再帮我一个忙吗,帮忙把你写得代码和这段代码整合成一个,能简化的地方就给他简化一点- Sub DeleteEmptyRow()
- Dim i%
- Dim p%
- Dim g As Integer
-
-
-
-
- Sheets.Add.Name = "辅食大件"
- Sheets.Add.Name = "辅食小件"
- Sheets.Add.Name = "米粉"
- Sheets("sheet3").Range("B1:C1000").Copy ThisWorkbook.Sheets("米粉").Range("A2")
- Sheets("sheet3").Range("E1:F1000").Copy ThisWorkbook.Sheets("辅食小件").Range("A2")
- Sheets("sheet3").Range("H1:L1000").Copy ThisWorkbook.Sheets("辅食大件").Range("A2")
-
- 'Application.DisplayAlerts = False
- 'Sheets("Sheet3").Delete
-
- 'Application.DisplayAlerts = True
-
-
-
- Sheets(1).Select
- Worksheets.Add '新建一个工作表
- Sheets(1).Name = "汇总工作表" '对新建工作表重命名
- For i = 2 To Sheets.Count 'For循环,遍历所有工作表
- Worksheets(i).Range("a1").CurrentRegion.Copy Destination:=Sheets(1).Range("a65536").End(xlUp).Offset(1) '粘贴到汇总工作中
- Next
- With Worksheets("汇总工作表")
-
- '自动删除空行
-
- For g = 1000 To 1 Step -1
- If .Cells(g, 10) = "" Or .Cells(g, 10) = "库位" Then
- .Rows(g).Delete
- End If
- Next g
-
- '自动删除空列和筛选的两行
-
- .Range("B:B,E:E").Delete
- .Range("a2:C" & Cells(Rows.Count, 5).End(3).Row).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
- End With
-
- '自动换行
-
-
- For p = 1 To 16000
- If Rows(p).RowHeight > 30 Then
- Rows(p).RowHeight = 15
- End If
- Next p
-
- '自动输公式
-
-
- n = [a65536].End(xlUp).Row
- [K1].Resize(n, 1).Formula = "=IF(IFERROR(VLOOKUP(C1,中谷签收!D:D,1,0),""中谷快运"")<>""中谷快运"",""中谷物流"",""中谷快运"")"
- [i1].Resize(n, 1).Formula = "=IF((LEFT(C1,4)&H1=""135201""),""产品"","""")&IF((LEFT(C1,4)&H1=""1352FJ""),""小听粉"","""")&IF((LEFT(C1,4)&H1=""H632FJ""),""小听粉"","""")&IF((LEFT(C1,4)&H1=""H63201""),""小听粉"","""")&IF((LEFT(C1,4)&H1=""121HFJ""),""赠品随货"","""")&IF((LEFT(C1,4)&H1=""121H01""),""赠品随货"","""")"
- [J1].Resize(n, 1).Formula = "=IF(H1=""01"",I1,"""")&IF(H1=""FJ"",I1,"""")"
-
- Cells.Select
- Cells.Copy
- Cells.PasteSpecial Paste:=xlPasteValues, Operation:=xlPasteSpecialOperationNone, SkipBlanks:=False, Transpose:=False
-
-
-
- '自动米粉
-
-
-
-
-
- Sheets("辅食大件").Activate
- Sheets("辅食大件").Range("a1") = "辅助字符"
- Sheets("辅食大件").Columns("a:a").Select
- Sheets("辅食大件").Range("a1:a10000").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
- n = [a65536].End(xlUp).Row - 1
- Sheets("辅食大件").[c2].Resize(n, 1).Formula = "=SUMIF(a:a,a2,b:b)"
-
-
- Sheets("辅食小件").Activate
- Sheets("辅食小件").Range("a1") = "辅助字符"
- Sheets("辅食小件").Columns("a:a").Select
- Sheets("辅食小件").Range("a1:a10000").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
- n = [a65536].End(xlUp).Row - 1
- Sheets("辅食小件").[c2].Resize(n, 1).Formula = "=SUMIF(a:a,a2,b:b)"
-
-
- Sheets("米粉").Activate
- Sheets("米粉").Range("a1") = "辅助字符"
- Sheets("米粉").Columns("a:a").Select
- Sheets("米粉").Range("a1:a10000").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
- n = [a65536].End(xlUp).Row - 1
- Sheets("米粉").[c2].Resize(n, 1).Formula = "=SUMIF(a:a,a2,b:b)"
-
- End Sub
复制代码 |
|