VBA宏使用Marlett检查删除未检查的行 [英] VBA Macro to delete unchecked rows using marlett check

问题描述

我在VBA中实际上没有太多的背景知识，但是我正在尝试创建一个宏，在该宏中，只要按一下按钮，就可以删除在一定范围内所有没有复选标记的行。我浏览了一些论坛，并了解了有关 marlett检查的信息，该字体中的字符 a显示为选中标记。这是我在适当范围内单击A列中的单元格时必须自动生成 marlett检查的代码：

I don't really have much of a background in VBA, but I'm trying to create a macro where, on the push of a button all rows that do not have a check mark in them in a certain range are deleted. I browsed some forums, and learned about a "marlett" check, where the character "a" in that font is displayed as a check mark. Here is the code I have to generate the "marlett check" automatically when clicking a cell in the A column in the appropriate range:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.Cells.Count > 1 Then Exit Sub
        If Not Intersect(Target, Range("A10:A111")) Is Nothing Then
            Target.Font.Name = "Marlett"
                If Target = vbNullString Then
                    Target = "a"
                Else
                    Target = vbNullString
                End If
        End If

End Sub

然后我有了另一个宏（分配给一个按钮），当该按钮为 A列时，该宏实际上删除了没有复选标记的行按下：

I then have another macro (assigned to a button) that actually deletes the rows without a check mark in the "A" column when the button is pressed:

Sub delete_rows()

Dim c As Range

On Error Resume Next
For Each c in Range("A10:A111")
    If c.Value <> "a" Then
        c.EntireRow.Delete
    End If
Next c

End Sub

一切正常，但是唯一的问题是在删除所有未选中的行之前，我必须多次按下按钮！看来我的循环运行不正常-有人可以帮忙吗？

Everything works, but the only problem is that I have to press the button multiple times before all of the unchecked rows are deleted!! It seems like my loop is not working properly -- can anyone please help??

谢谢！

推荐答案

我认为这可能是由于您删除行的方式所致，每次删除后您可能会跳过一行。

I think this may be due to how you're deleting the rows, you might be skipping a row after every delete.

您可能想将您的for-each更改为常规的for循环。因此您可以控制正在处理的索引。 查看此答案或该问题的其他答案以了解如何操作。

You might want to change your for-each for a regular for loop. so you can control the index you'r working on. see this answer or the other answers to the question to see how to do it.

这里有一个适合您（可能）问题的修改版本。

Heres a modified version that should suit your (possible) problem.

Sub Main()
    Dim Row As Long
    Dim Sheet As Worksheet
    Row = 10
    Set Sheet = Worksheets("Sheet1")
    Application.ScreenUpdating = False
    Do
        If Sheet.Cells(Row, 1).Value = "a" Then
            'Sheet.Rows(Row).Delete xlShiftUp
            Row = Row + 1
        Else
            'Row = Row + 1
            Sheet.Rows(Row).Delete xlShiftUp
        End If
    Loop While Row <= 111
    Application.ScreenUpdating = True
End Sub

更新
尝试编辑对if块进行一些猜测。当我精通时，将对其进行查看。

无论建议的更改如何，它都会进入无限循环。
问题是当它接近数据末尾时，它会不断发现空行（因为没有更多数据！），所以它会不断删除它们。

It does go into an infinite loop regardless of the suggested change. The problem was when it got near the end of your data it continually found empty rows (as theres no more data!) so it kept deleting them.

但是下面的代码应该可以工作。

The code below should work though.

Sub Main()
    Dim Row As Long: Row = 10
    Dim Count As Long: Count = 0
    Dim Sheet As Worksheet
    Set Sheet = Worksheets("Sheet1")
    Application.ScreenUpdating = False
    Do
        If Sheet.Cells(Row, 1).Value = "a" Then
            Row = Row + 1
        Else
            Count = Count + 1
            Sheet.Rows(Row).Delete xlShiftUp
        End If
    Loop While Row <= 111 And Row + Count <= 111
    Application.ScreenUpdating = True
End Sub

这篇关于VBA宏使用Marlett检查删除未检查的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！