如何删除"A"列中有数字的行? [英] How to delete rows where there is a number In Column "A"?

问题描述

在"A"列中，我试图删除以数字开头的行.因此，例如，在我发布的图像中，我希望宏删除第3行和第2行；11.

In Column "A" I am trying to delete rows that begin with a number. So for example, in the image that I have posted, I want my macro to delete rows # 3 & 11.

 Sub RemoveNumbersFromColumnA()
  Dim X As Long, R As Long, Data As Variant
  Data = Range("A3", Cells(Rows.Count, "A").End(xlUp))
  For R = 3 To UBound(Data)
    For X = 3 To Len(Data(R, 3))
      If Mid(Data(R, 3), X, 3) Like "[0-9]" Then

      Rows("3:" & cell.Row - 1).Delete
Next
      Range("A3").Resize(UBound(Data)) = Data
Else if
End Sub

推荐答案

从工作缓慢的东西开始就是这样的:

Starting with something that works slowly is this one:

Sub RemoveNumbersFromColumnA()

    Dim i As Long
    Dim wks As Worksheet
    Set wks = Worksheets(1)

    For i = 10 To 1 Step -1
        If IsNumeric(Left(wks.Cells(i, "A"), 1)) Then
            Debug.Print wks.Cells(i, "A"); " deleting..."
            wks.Cells(i, "A").Delete
        End If
    Next i

End Sub

该想法是向后循环并检查单元格内容的最左边部分是否为数字.如果是数字，则在立即窗口中打印信息并将其删除.这就是您所得到的，例如按 F8 .逐步:

The idea is to loop backwards and to check whether the leftmost part of the cell content is numeric. If it is numeric, then print the info in the immediate window and delete it. This is what you get, pressing F8, e.g. step-by-step:

然后，您可以继续，将要删除的行保存到单个范围，最后只删除一次该范围.像这样一个:

Then you may continue, as saving the rows to be deleted to a single range and only deleting this range once at the end. Like this one:

Sub RemoveNumbersFromColumnA()

    Dim i As Long
    Dim myCell As Range
    Dim rowsToDelete As Range

    For i = 1 To 10
        Set myCell = Worksheets(1).Cells(i, "A")
        If IsNumeric(Left(myCell, 1)) Then
            Debug.Print myCell; " adding..."
            If rowsToDelete Is Nothing Then
                Set rowsToDelete = myCell.EntireRow
            Else
                Set rowsToDelete = Union(rowsToDelete, myCell.EntireRow)
            End If
        End If
    Next i

    If Not rowsToDelete Is Nothing Then
        rowsToDelete.Delete
    End If

End Sub

这篇关于如何删除"A"列中有数字的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！