关于excel自动删除数据的问题

joewoo 2004-09-03 01:30:11

现有excel表sheet1,表中有一个9X1700的矩阵，矩阵中的元素为1至5中的整数。我想把一行全是“5”的行自动找出并删除，请问有什么方法？
请把具体步骤写出，如果要用VBA，请把语句写出来。本人懂VB编程，但从未研究过VBA，不清楚两者间的不同。

...全文

90 点赞收藏 5

写回复

5 条回复

切换为时间正序

当前发帖距今超过3年，不再开放新的回复

发表回复

RUKYO 2004-09-05

另一种方法，是可彻底删除的，引用Excel对象库，非Excel宏形式:

'工程->引用Microsoft Excel x.0 Object Library
Private Sub Command1_Click()
Dim mFind, FirstAddress
Dim mCount As Integer
Dim mRemRow
Dim mSaveFind() As Long
Dim mContRow As Long
Dim i As Long

Dim mRange As Range
Set xlApp = CreateObject("Excel.Application") '创建EXCEL应用类
xlApp.Visible = True '设置EXCEL可见
Set xlBook = xlApp.Workbooks.Open(App.Path & "\Book1.xls") '打开EXCEL工作簿
Set xlsheet = xlBook.Worksheets(1) '打开EXCEL工作表
xlsheet.Activate '激活工作表

xlsheet.Range("A2:I15").Select
With xlsheet.Range("A2:I15")
Set mFind = xlsheet.Cells.Find(What:=5, After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, MatchByte:=True)
If Not mFind Is Nothing Then
mCount = 1
mContRow = 0
mRemRow = mFind.Row
FirstAddress = mFind.Address
Do
If mRemRow = mFind.Row And mCount < 8 Then
mCount = mCount + 1
Else
If mRemRow = mFind.Row And mCount = 8 Then
ReDim Preserve mSaveFind(mContRow)
mSaveFind(mContRow) = mFind.Row
mContRow = mContRow + 1
Else
If mRemRow <> mFind.Row Then
mCount = 1
mRemRow = mFind.Row
End If
End If
End If
Set mFind = .FindNext(mFind)
Loop While mFind.Address <> FirstAddress
End If
End With

If mContRow <> 0 Then
Worksheets("sheet1").Activate
Set mRange = Range("A" & CStr(mSaveFind(0)) & ":" & "I" & CStr(mSaveFind(0)))
For i = 1 To UBound(mSaveFind())
Set mRange = Union(mRange, Range("A" & CStr(mSaveFind(i)) & ":" & "I" & CStr(mSaveFind(i))))
Next i
mRange.Select
Selection.Delete Shift:=xlUp
End If
xlBook.Close (True) '关闭EXCEL工作簿
xlApp.Quit '关闭EXCEL
Set xlApp = Nothing '释放EXCEL对象
End Sub

回复赞

chenghua 2004-09-03

回复赞

RUKYO 2004-09-03

删除

删除 Excel 数据时，受到的限制要比从关系数据源中删除数据时更多。在关系数据库中，“行”除了表示一条“记录”外没有其他意义；但在 Excel 工作表中却不同。可以删除字段（单元格）中的值。但不能：
一次删除一整条记录，否则将出现以下错误信息：

Deleting data in a linked table is not supported by this ISAM.
只能通过分别清空各个字段的内容来删除一条记录。
删除包含 Excel 公式的单元格中的值，否则将出现以下错误信息：

Operation is not allowed in this context.
虽然电子表格中已被删除的数据原来所在的行现在是空行，但无法将其删除，而且记录集将继续显示对应于这些空行的空记录。

回复赞

RUKYO 2004-09-03

上面的方法是假定你Sheet1中只有9X1700矩阵的数据

这种方式不能把链接的Excel表中每行数据像记录一样整行删除，只能把符合条件（都是5）的行各字段赋值为空。

若果楼主想要彻底除去，我的想法是把excel导出到access中删除后再导会excel，不过此法很烦琐。

回复赞

RUKYO 2004-09-03

'用ADO 来处理 Excel 数据
'工程-〉引用Microsoft ActiveX Data Objects 2.x Library
Option Explicit
Public mCnnString As String

Private Sub Form_Load()
mCnnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source= " & App.Path & "\Book1.xls;" & "Extended Properties=""Excel 8.0;HDR=Yes;"";"
End Sub

Private Sub Command1_Click()
Dim mCon As New ADODB.Connection
Dim mRst As New ADODB.Recordset
Dim mField(8) As String
Dim i As Integer
mCon.CursorLocation = adUseClient
mCon.Open mCnnString
mRst.Open "Select * From [Sheet1$]", mCnnString, adOpenKeyset, adLockOptimistic, adCmdText
For i = 0 To mRst.Fields.Count - 1
mField(i) = mRst.Fields.Item(i).Name
Next i
mCon.Execute "Update [Sheet1$] Set " & mField(0) & " = Null ," & mField(1) & " = Null ," & mField(2) & " = Null ," & mField(3) & " = Null ," & mField(4) & " = Null ," & mField(5) & " = Null ," & mField(6) & " = Null ," & mField(7) & " = Null ," & mField(8) & " = Null " & "Where " & mField(0) & "= 5 And " & mField(1) & "= 5 And " & mField(2) & "= 5 And " & mField(3) & "= 5 And " & mField(4) & "= 5 And " & mField(5) & "= 5 And " & mField(6) & "= 5 And " & mField(7) & "= 5 And " & mField(8) & "= 5 "
Set mRst = Nothing
Set mCon = Nothing
End Sub

回复赞