0

Am having a slight malfunction with this line of codes. I intend for this code to loop and delete rows of which either cells in column A or cells in column B is empty.. This rows contains Clients Names in Column A and The Clients Number in Column B. Now when i execute the script, the first row deletes fine, but from the next row which ought to delete, the phone number begins to mix up.

Please, i just need the argument to be written in such a way it fetches all row which are true according to the conditions and delete, or any logic which might work, without mixing up datas Thank You.

NOTE: I would appreciate if correction or upgrade is done using my code below. Thank you 

Dim rngRange As Range
    Dim rngUnion As Range
    Dim rngCEll As Range
    Set rngRange = Sheet2.UsedRange.Offset(7)
    For Each rngCEll In rngRange.Columns(1).Cells
        If rngCEll.Value = "" Or rngCEll.Offset(, 1).Value = "" Then
            If rngUnion Is Nothing Then
                Set rngUnion = rngCEll
            Else
                Set rngUnion = Union(rngUnion, rngCEll)
            End If
            End If
    Next rngCEll
    If Not rngUnion Is Nothing Then
        rngUnion.EntireRow.Delete
    End If
Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
user74651
  • 55
  • 7
  • 2
    No need to loop. Use autofilter. I have answered many similar questions in the past. Please do a search – Siddharth Rout Sep 19 '19 at 01:44
  • 3
    [Here](https://stackoverflow.com/questions/11317172/delete-row-based-on-condition) is one such link – Siddharth Rout Sep 19 '19 at 01:46
  • Ok, thank you.. let me try it out – user74651 Sep 19 '19 at 01:54
  • 2
    If you use a loop and you delete rows/cols, always work backwards – alowflyingpig Sep 19 '19 at 02:03
  • Ok.. i tried using your sample from the link you provided, to filter 2 columns that starts from A7 and B7 respectively downward.. I got an error that says "The command could not be completed by using the range specified. select a single cell within the range and try again". – user74651 Sep 19 '19 at 02:04
  • Dim ws As Worksheet Dim strSearch As String Dim lRow As Long strSearch = "" Set ws = Sheets("Sheet1") With ws lRow = .Range("A7" & .Rows.Count, "B7" & .Rows.Count).End(xlUp).Row '~~> Remove any filters .AutoFilterMode = False With .Range("A7:A" & lRow) .AutoFilter Field:=1, Criteria1:="=*" & strSearch & "*" .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete End With '~~> Remove any filters .AutoFilterMode = False End With – user74651 Sep 19 '19 at 02:06
  • 3
    You could [edit] that code into your question, it would be so much prettier than in the comments section! – Mathieu Guindon Sep 19 '19 at 03:01

0 Answers0