根据使用公式找到的值，使用VBA删除excel中的行

问题描述

我正在尝试编写一个代码，删除具有使用公式找到的值的行。问题是每一行都是一个 #VALUE！，由于报告的设置，我无法更改。最后，我想删除所有具有 #VALUE！的行，并且在列H中具有小于.75值的任何行。 code>。

我尝试的代码如下所示：

  Private Sub CommandButton1_Click（）
 Dim rng As Range，cell As Range，del As Range 
 Set rng = Intersect（Range（H1：H2000），ActiveSheet.UsedRange）
对于每个单元格在rng 
 If（cell.Value）< .75然后
如果del is Nothing然后
设置del =单元格
否则：设置del =联合（del，单元格）
如果
结束If 
下一个单元格
错误简历Next 
 del.EntireRow.Delete 
 End Sub 
  

任何帮助或提示将不胜感激。

解决方案

我的代码是其他答案的替代品，更高效，执行速度更快，然后分别删除每一行:)给它一个

  Option Explicit 
 
 Sub DeleteEmptyRows（）
 Application.ScreenUpdating = False 
 
 Dim ws As Worksheet 
 Dim i& lr& rowsToDelete $，lookFor $，lookFor2 $ 
 
'* !!! *设置行删除的条件
 lookFor =#VALUE！ 
 lookFor2 =0.75
 
设置ws = ThisWorkbook.Sheets（Sheet1）
 lr = ws.Range（H& Rows.Count）.End xlUp）.Row 
 
 ReDim arr（0）
 
对于i = 1 To lr 
如果StrComp（CStr（ws.Range（H& i ）.Text），lookFor，vbTextCompare）= 0或_ 
 CDbl（ws.Range（H& i）.Value） CDbl（lookFor2）然后
 ReDim保存arr（UBound（arr）+ 1）
 arr（UBound（arr） -  1）= i 
结束如果
下一个i 
 
如果UBound（arr）> 0然后
 ReDim保存arr（UBound（arr） -  1）
对于i = LBound（arr）到UBound（arr）
 rowsToDelete = rowsToDelete& arr（i）& ：& arr（i）& ，
 Next i 
 
 ws.Range（Left（rowsToDelete，Len（rowsToDelete） -  1））。删除Shift：= xlUp 
 Else 
应用程序。 ScreenUpdating = True 
 MsgBox不再有行包含：& lookFor& 或& lookFor2& ，因此退出
 Exit Sub 
 End If 
 
如果不是Application.ScreenUpdating Then Application.ScreenUpdating = True 
设置ws = Nothing 
 End Sub 
  

Hey guys I am trying to write a code that deletes rows having values that are found using a formula. The problem is every other row is a #VALUE!, which I cannot change due to the setup of the report. In the end I want to delete all rows that have #VALUE! and any row that has values that are less than .75 in Column H.

The code I tried is as shown below:

Private Sub CommandButton1_Click()
    Dim rng As Range, cell As Range, del As Range
    Set rng = Intersect(Range("H1:H2000"), ActiveSheet.UsedRange)
    For Each cell In rng
        If (cell.Value) < .75 Then
            If del Is Nothing Then
                Set del = cell
            Else: Set del = Union(del, cell)
            End If
        End If
    Next cell
    On Error Resume Next
    del.EntireRow.Delete
End Sub

Any help or tips would be appreciated.

解决方案

my code is an alternative to the other answers, its much more efficient and executes faster then deleting each row separately :) give it a go

Option Explicit

Sub DeleteEmptyRows()
    Application.ScreenUpdating = False

    Dim ws As Worksheet
    Dim i&, lr&, rowsToDelete$, lookFor$, lookFor2$

    '*!!!* set the condition for row deletion
    lookFor = "#VALUE!"
    lookFor2 = "0.75"

    Set ws = ThisWorkbook.Sheets("Sheet1")
    lr = ws.Range("H" & Rows.Count).End(xlUp).Row

    ReDim arr(0)

    For i = 1 To lr
     If StrComp(CStr(ws.Range("H" & i).Text), lookFor, vbTextCompare) = 0 Or _
        CDbl(ws.Range("H" & i).Value) < CDbl(lookFor2) Then
        ReDim Preserve arr(UBound(arr) + 1)
        arr(UBound(arr) - 1) = i
     End If
    Next i

    If UBound(arr) > 0 Then
        ReDim Preserve arr(UBound(arr) - 1)
        For i = LBound(arr) To UBound(arr)
            rowsToDelete = rowsToDelete & arr(i) & ":" & arr(i) & ","
        Next i

        ws.Range(Left(rowsToDelete, Len(rowsToDelete) - 1)).Delete Shift:=xlUp
    Else
        Application.ScreenUpdating = True
        MsgBox "No more rows contain: " & lookFor & "or" & lookFor2 & ", therefore exiting"
        Exit Sub
    End If

    If Not Application.ScreenUpdating Then Application.ScreenUpdating = True
    Set ws = Nothing
End Sub

这篇关于根据使用公式找到的值，使用VBA删除excel中的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！