宏删除删除ECEL表格行列
篇一：Excel删除A列空行宏代码
Sub 删除A列空行()
Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete End Sub
篇二：Excel工作表到时自动删除公式的宏代码
用宏删除EXCEL中的公式
Private Sub Workbook_Open()
if date>=-1-1# then
For i = 1 To Sheets.Count
Sheets(i).Activatedelete删除表格还是内容
Cells.Copy
Cells.PasteSpecial xlPasteValues
Next i
Sheets(1).Activate
end if
Option Explicit
Sub RmvMacros()
Dim wbk As Workbook
Dim str As String
ThisWorkbook.SaveCopyAs "D:\另存备份.xls"
str = ThisWorkbook.Path & "\另存备份.xls" '要删除宏的文件名
Application.EnableEvents = False '禁止在打开时触发事件
Set wbk = Workbooks.Open(str)
RemoveAllMacros wbk '调用RemoveAllMacros删除宏代码
wbk.Close savechanges:=True
Application.EnableEvents = True
Sub RemoveAllMacros(wbk As Workbook) '参数wbk为要删除宏的工作簿
Dim i As Long
Dim vbc As VBComponent
For Each vbc In wbk.VBProject.VBComponents '遍历wbk工作簿的每一个模块
If vbc.Type = vbext_ct_Document Then '如果是Excel对象的模块,则清除其中的代码,否则删除整个模块
vbc.CodeModule.DeleteLines 1, vbc.CodeModule.CountOfLines
Else
wbk.VBProject.VBComponents.Remove vbc
End If
Next vbc
Sub ListAllCodeModule()
Dim strVBCmpType As String
Dim vbc As VBComponent
Debug.Print "名称类型 代码行数"
For Each vbc In ThisWorkbook.VBProject.VBComponents
With vbc
Select Case .Type
Case vbext_ct_Document
strVBCmpType = "Excel 对象"
Case vbext_ct_StdModule
strVBCmpType = "模块"
Case vbext_ct_MSForm
strVBCmpType = "窗体"
Case vbext_ct_ClassModule
strVBCmpType = "类模块"
End Select
Debug.Print .Name & Space(20 - Len(.Name)), strVBCmpType, .CodeModule.CountOfLines
End With
Next vbc
用宏代码清除excel2000文档中的宏代码、部分控件 'removeExcelMacro("Book1.Xls",Array("CheckBox1","TextBox1","ListBox"))
'
'直接删除目标文件的宏代码和控件(可选择保留的控件),Excel文件名称、要删除的控件名称数组
Public Static Function removeExcelMacro(targetExcel As String, killOleObjectType As Variant) As Boolean
On Error GoTo ErrHand
Dim i, j, n As Byte
Dim vbeComp As New VBComponents
Dim vbaObje As OLEObject
removeExcelMacro = False
Set vbeComp = Application.Workbooks(targetExcel).VBProject.VBComponents
n = vbeComp.Count
For i = 1 To n
If i > vbeComp.Count Then Exit For
If vbeComp(i).Type = 100 Then'100: xl_Document_Type(Include Workbook , Worksheet) '删除代码
If vbeComp(i).CodeModule.CountOfLines > 0 Then vbeComp(i).CodeModule.DeleteLines 1, vbeComp(i).CodeModule.CountOfLines
'删除控件
vbeComp(i).Activate
If killOleObjectType(0) <> "" Then
For Each vbaObje In ActiveSheet.OLEObjects
For j = 0 To UBound(killOleObjectType)
If UCa(来自: 小龙 文档 网:宏删除删除excel表格行列)se(Split(vbaObje.ProgId, ".")(1)) = UCase(killOleObjectType(j)) Then
vbaObje.Select: Selection.Delete
End If
Next
Next
End If
Else
'删除整个模块
vbeComp.Remove vbeComp(i)
i = i - 1
End If

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。