查找所有内容并在VBA中删除 [英] Find all and delete in VBA

问题描述

我正在寻找一种查找所有内容的方法-在VBA中的一列中，存储它们还是删除行?

I am looking for a way to find all - in a column in VBA, store them or and delete the rows?

这是我可以用来查找和删除单行的内容.但是我需要找到所有的破折号.

Here is what I can use to find and delete a single row. But I need to find all of the dashes.

Dim A as Long

    Columns("C:C").Select
    Selection.Find(What:="-", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
     A = ActiveCell.Row
     Range(A).Delete

我想到了一个循环，但是由于破折号的数量会变化，因此我不知道要运行多少次迭代.

I thought about a loop but I don't know how many times to run the iteration as the number of dashes will vary.

任何建议将不胜感激.

谢谢

更新-我不确定如何进行迷你降价并使其可读，所以这里是更新的代码.

Update- I'm not sure how to do mini markdown and make it readable so here is the updated code.

我不断收到以下错误:'ObjectVariable或With块变量未设置'

I keep getting the following error: 'ObjectVariable or With block Variable not set'

Dim LastRow As Long

'Find the last used row in a Column: column A
 With ActiveSheet
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
 End With

'
 With Worksheets(5).Range("c1:c1500")
    Set c = .Find("-", LookIn:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            c.Value = "John"
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> LastRow
    End If
End With

推荐答案

如聊天记录所述，此代码有效:

As follows up from chat, this code is working:

Sub test()
    Dim firstAddress As String
    Dim c As Range
    Dim rngToDelete As Range

    With Worksheets(5).Range("c1:c1500")
       Set c = .Find("-", LookIn:=xlValues)
       If Not c Is Nothing Then
           firstAddress = c.Address
           Do
               If rngToDelete Is Nothing Then
                  Set rngToDelete = c
               Else
                  Set rngToDelete = Union(rngToDelete, c)
               End If
               Set c = .FindNext(c)
               If c Is Nothing Then Exit Do
           Loop While c.Address <> firstAddress
       End If
    End With

    If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Delete
End Sub

这篇关于查找所有内容并在VBA中删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！