关于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
UP
回复
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
回复
相关推荐
发帖

1188

社区成员

VB 数据库(包含打印,安装,报表)
申请成为版主
帖子事件
创建了帖子
2004-09-03 01:30
社区公告
暂无公告