如何在多工作表中(撤销工作薄保护\删除满足要求的行\插入行\计算平均值等)

kaiser1103

一下是我按照我的需求修改后的
因为我对vba不熟悉,code也是看到别人写的实现的功能,然后搬过来,有的是自己录制宏,再把code搬过来.
我想在这里再修改一下,把内容为 100%  的单元格所在的行删除掉,改成:如果单元格的内容  大于 20% ,就删除这一行.
------------------------------------------------------------------
Sub Macro1()

' Macro recorded 2009-7-29 by FangMo

' 快捷键: Ctrl+r
'
'处理电信表

ActiveSheet.Unprotect
Sheets("电信").Select
ActiveSheet.Unprotect

Dim Lrow As Long, Irow As Long
Dim Rng

    Irow = Range("B65536").End(xlUp).Row

    ActiveSheet.Unprotect
     
    Rows("3:3").Select
    Range("D3").Activate
    Selection.AutoFilter
    Selection.AutoFilter Field:=7, Criteria1:="100%"
    Range("B6").Activate
    Rows("4:" & Irow).Select
    Selection.Delete Shift:=xlUp
    Selection.AutoFilter
   
    Range("G4:G" & Irow).NumberFormatLocal = "0.00%"
   
    Columns("G:G").Select
    For Each Rng In Selection.Areas
         Rng.Value = Rng.Value
    Next
   
  Range("A4").Select
  
  Lrow = Range("A65536").End(xlUp).Row
  


  Rows(Lrow & ":" & Lrow).Insert Shift:=xlDown
  
  
  Range("F" & Lrow).Select
  ActiveCell.FormulaR1C1 = "=AVERAGE(R[" & 4 - Lrow & "]C:R[-1]C)"
  Range("G" & Lrow).Select
  ActiveCell.FormulaR1C1 = "=AVERAGE(R[" & 4 - Lrow & "]C:R[-1]C)"

    Range("A" & Lrow & ":G" & Lrow).Select
    With Selection.Interior
        .ColorIndex = 45
        .Pattern = xlSolid
    End With


  Irow = Range("B65536").End(xlUp).Row + 1
  Range("F" & Irow).Select
  ActiveCell.FormulaR1C1 = "=AVERAGE(R[" & Lrow - Irow + 1 & "]C:R[-1]C)"
  Range("G" & Irow).Select
  ActiveCell.FormulaR1C1 = "=AVERAGE(R[" & Lrow - Irow + 1 & "]C:R[-1]C)"

    Range("A" & Irow & ":G" & Irow).Select
    With Selection.Interior
         .ColorIndex = 45
         .Pattern = xlSolid
    End With
   
'处理网通表

ActiveSheet.Unprotect
Sheets("网通").Select
ActiveSheet.Unprotect

    Irow = Range("B65536").End(xlUp).Row

    ActiveSheet.Unprotect
     
    Rows("3:3").Select
    Range("D3").Activate
    Selection.AutoFilter
    Selection.AutoFilter Field:=7, Criteria1:="100%"
    Range("B6").Activate
    Rows("4:" & Irow).Select
    Selection.Delete Shift:=xlUp
    Selection.AutoFilter
   
    Range("G4:G" & Irow).NumberFormatLocal = "0.00%"
   
    Columns("G:G").Select
    For Each Rng In Selection.Areas
         Rng.Value = Rng.Value
    Next
   
  Range("A4").Select
  
  Lrow = Range("A65536").End(xlUp).Row
  


  Rows(Lrow & ":" & Lrow).Insert Shift:=xlDown
  
  
  Range("F" & Lrow).Select
  ActiveCell.FormulaR1C1 = "=AVERAGE(R[" & 4 - Lrow & "]C:R[-1]C)"
  Range("G" & Lrow).Select
  ActiveCell.FormulaR1C1 = "=AVERAGE(R[" & 4 - Lrow & "]C:R[-1]C)"

    Range("A" & Lrow & ":G" & Lrow).Select
    With Selection.Interior
        .ColorIndex = 45
        .Pattern = xlSolid
    End With


  Irow = Range("B65536").End(xlUp).Row + 1
  Range("F" & Irow).Select
  ActiveCell.FormulaR1C1 = "=AVERAGE(R[" & Lrow - Irow + 1 & "]C:R[-1]C)"
  Range("G" & Irow).Select
  ActiveCell.FormulaR1C1 = "=AVERAGE(R[" & Lrow - Irow + 1 & "]C:R[-1]C)"

    Range("A" & Irow & ":G" & Irow).Select
    With Selection.Interior
         .ColorIndex = 45
         .Pattern = xlSolid
    End With

End Sub
--------------------------End Code-----------------------


Range("G4:G" & Irow).NumberFormatLocal = "0.00%"

这一句能不能改成:
Range("G4:G" & Irow).NumberFormatLocal > R[20%]C

kaiser1103

写错了
应该是这里要改

Selection.AutoFilter Field:=7, Criteria1:="100%"

我想改成,>20% 的就删除行

ZFL68622888

看看，学习下