判断一列中=0所在的行，并删除，几十万条数据

shenjianrong163

参照opiona的思路：
Sub test()
    Dim Conn As Object, Rst As Object
    Dim strConn As String, strSQL As String, Arr
    Dim r, Pathstr As String
    Pathstr = ThisWorkbook.FullName '文件路径,工作簿名
    Set Conn = CreateObject("ADODB.Connection")
    Set Rst = CreateObject("ADODB.Recordset")
    Select Case Application.Version * 1
        Case Is <= 11
            strConn = "Provider=Microsoft.Jet.Oledb.4.0;Extended Properties='Excel 8.0;HDR=YES';Data Source=" & Pathstr
        Case Is >= 12
            strConn = "provider=Microsoft.ACE.Oledb.12.0;Data Source=" & Pathstr & ";Extended Properties=""Excel 12.0;HDR=YES"";"""
    End Select
    Conn.Open strConn
    strSQL = "select 供应商代码,商品代码,单价 from [" & "Sheet0" & "$A1:C]" & " where NOT 单价 IS NULL AND 单价 <> 0 " '设置范围
    Set Rst = Conn.Execute(strSQL)
    Arr = Conn.Execute(strSQL).GetRows
    Conn.Close
    Set Conn = Nothing
    Set Rst = Nothing
    With Worksheets("Sheet0")
        r = .Cells(.Rows.Count, 1).End(xlUp).Row
        .Range("A2:C" & r).ClearContents
        .Range("A:B").NumberFormatLocal = "@"
        .Cells(2, 1).Resize(UBound(Arr, 2) + 1, UBound(Arr, 1) + 1) = Application.Transpose(Arr)
    End With
End Sub

    请看附件： 取单价非零的值.rar (1.52 MB, 下载次数: 2)

2020-1-30 01:26 上传

点击文件名下载附件
删除零值的行

lyt1979

Sub deledata()
   
    Dim i As Long
   
    For i = 59372 To 1 Step -1   '最后一行行号
        If Range("c" & i) = 0 Then
            Range("c" & i).Select
            Selection.EntireRow.Delete
        End If
    Next
   
   
End Sub

1007467564

lyt1979 发表于 2020-1-30 11:10
Sub deledata()
   
    Dim i As Long

可以简化如下：
Sub deledata()
   
    Dim i As Long
   
    For i = Range("c65536").End(xlUp).Row To 1 Step -1    'Range("c65536").End(xlUp).Row,?????
        If Cells(i, 3) = 0 Then
            Rows(i).Delete
        End If
    Next
End Sub

lyt1979

1007467564 发表于 2020-1-30 14:46
可以简化如下：
Sub deledata()
   

非常感谢，我的代码还是太机械化了