如果行A中没有数据，尝试删除一行：J [英] Trying to delete a row if no data in row A:J

问题描述

如果没有 A中的数据，我正在尝试删除一行：J

我发现这个代码一直在尝试编辑它，但这是最终删除整个工作表的数据。

任何帮助将不胜感激

  Sub DeleteRows（）
 Dim rngBlanks As Range 
 Dim i As Integer 
 
对于i = 1至10 
 On Error Resume Next 
设置rngBlanks =列（i）.SpecialCells（xlCellTypeBlanks）
错误GoTo 0 
如果没有rngBlanks是没有
 rngBlanks.EntireRow.Delete 
结束如果
下一个
结束Sub 
  

解决方案

如果行A中没有数据，尝试删除一行：J

什么代码正在做的是单独检查列，而不是您的标题建议的范围 A：J 。由于这个原因，您的整个数据可能会被删除。让我们说 A1 有一些数据，但是 B1 没有。所以你的代码将删除第1行。你要做的是检查是否说 A1：J1 是空白的。

我认为这是你正在尝试？

  Option Explicit 
 
子样本（）
 Dim ws As Worksheet 
 Dim rngBlanks As Range 
 Dim i As Long，lRow As Long，Ret As Long 
 
'~~>将此设置为相关工作表
设置ws = ThisWorkbook.Sheets（Sheet2）
 
与ws 
'~~>获取该工作表中的最后一行
如果Application.WorksheetFunction.CountA（.Cells）<> 0然后
 lRow = .Cells.Find（What：=*，_ 
 After：=。Range（A1），_ 
 Lookat：= xlPart，_ 
 LookIn：= xlFormulas，_ 
 SearchOrder：= xlByRows，_ 
 SearchDirection：= xlPrevious，_ 
 MatchCase：= False）.Row 
 Else 
 lRow = 1 
结束如果
 
'~~>循环遍历行以查找哪个范围为空
对于i = 1 To lRow 
 Ret = Application.Evaluate（= COUNTA（A& i&：J& ））
如果Ret = 0然后
如果rngBlanks是Nothing然后
设置rngBlanks = .Rows（i）
 Else 
设置rngBlanks = Union（rngBlanks， .Rows（i））
 End If 
 End If 
 Next i 
 End With 
 
'~~~>删除范围
如果不是rngBlanks是没有，然后rngBlanks.Delete 
 End Sub 
  

另一种方式是使用Autofilter删除这些范围

I am trying to delete a row if there is no data from A:J

I have found this code and been trying to edit it, but this is deleting the whole sheet's data eventually.

Any help would be greatly appreciated

 Sub DeleteRows()
    Dim rngBlanks As Range
    Dim i As Integer

    For i = 1 To 10
        On Error Resume Next
        Set rngBlanks = Columns(i).SpecialCells(xlCellTypeBlanks)
        On Error GoTo 0
        If Not rngBlanks Is Nothing Then
            rngBlanks.EntireRow.Delete
        End If
    Next
 End Sub

解决方案

Trying to delete a row if no data in row A:J

What code is doing is individually checking the columns and not the range A:J as your title suggests. It is very much possible that your entire data is getting deleted because of this. Lets say A1 has some data but B1 doesn't. So your code will delete Row 1. What you have to do is to check if say A1:J1 is blank.

I think this is what you are trying?

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim rngBlanks As Range
    Dim i As Long, lRow As Long, Ret As Long

    '~~> Set this to the relevant worksheet
    Set ws = ThisWorkbook.Sheets("Sheet2")

    With ws
        '~~> Get the last row in that sheet
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            lRow = .Cells.Find(What:="*", _
                          After:=.Range("A1"), _
                          Lookat:=xlPart, _
                          LookIn:=xlFormulas, _
                          SearchOrder:=xlByRows, _
                          SearchDirection:=xlPrevious, _
                          MatchCase:=False).Row
        Else
            lRow = 1
        End If

        '~~> Loop through the rows to find which range is blank
        For i = 1 To lRow
            Ret = Application.Evaluate("=COUNTA(A" & i & ":J" & i & ")")
            If Ret = 0 Then
                If rngBlanks Is Nothing Then
                    Set rngBlanks = .Rows(i)
                Else
                    Set rngBlanks = Union(rngBlanks, .Rows(i))
                End If
            End If
        Next i
    End With

    '~~~> Delete the range
    If Not rngBlanks Is Nothing Then rngBlanks.Delete
End Sub

Another way would be to use Autofilter to delete those ranges

这篇关于如果行A中没有数据，尝试删除一行：J的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！