This code errors with 1 or 2 rows of data, but doesn't error with 3 or more rows of data, why?

[VBAProIWish]

Hello All,

I get "run time error 1004"
No Cells were found

When I run the code below. Strangely, it only gives me this error message when there are only 1 or 2 rows of data (not including the header row of course).

When there are 3 or more rows of data, I do NOT get this error and the macro runs perfectly!

The code in red below represents where the error bugs in the code.

Can this be fixed to account for any amount of rows?

  Dim Cust As Range
  Dim Blanks As Range
  Dim Key As Variant
  Dim DSO As Object
  Dim Rng As Range
  Dim RngEnd As Range
 
      Rows("4:4").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.Insert Shift:=xlDown
 
    Set Cust = ActiveSheet.Rows(1).Find("Customer Number", , xlValues, xlPart, xlByRows, xlNext, False)
 
      Set Rng = Cust.Offset(1, 0)
      Set RngEnd = Rng.Parent.Cells(Rows.Count, Rng.Column).End(xlUp)
      Set Cust = IIf(RngEnd.Row < Rng.Row, Rng, Rng.Parent.Range(Rng, RngEnd))
 
      Application.ScreenUpdating = False
      Set DSO = CreateObject("Scripting.Dictionary")
      DSO.CompareMode = vbTextCompare
 
        For Each Cell In Cust.Cells
          Key = Trim(Cell)
          If Not DSO.exists(Key) Then
             DSO.Add Key, 1
          Else
             Cell.ClearContents
          End If
        Next Cell
 
        If DSO.Count > 0 Then
[B][SIZE=3][COLOR=red]      Set Blanks = Cust.SpecialCells(xlCellTypeBlanks)[/COLOR][/SIZE][/B]
          Blanks.EntireRow.Delete
        End If
 
      Set DSO = Nothing
      Application.ScreenUpdating = True
 
 End Sub

The code in red above represents where the error bugs.

The sample WS below shows what the WS looked like at the time it bugged.

The blue highlight in row 4 represents that row 4 was selected at the time of the error...

<TABLE style="WIDTH: 859pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=1143 border=0><COLGROUP><COL style="WIDTH: 79pt; mso-width-source: userset; mso-width-alt: 3840" width=105><COL style="WIDTH: 192pt; mso-width-source: userset; mso-width-alt: 9362" width=256><COL style="WIDTH: 99pt; mso-width-source: userset; mso-width-alt: 4827" width=132><COL style="WIDTH: 73pt; mso-width-source: userset; mso-width-alt: 3547" width=97><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 4461" width=122><COL style="WIDTH: 97pt; mso-width-source: userset; mso-width-alt: 4717" width=129><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2925" width=80><COL style="WIDTH: 167pt; mso-width-source: userset; mso-width-alt: 8118" width=222><TBODY><TR style="HEIGHT: 30pt" height=40><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 79pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 30pt; BACKGROUND-COLOR: #d8d8d8" width=105 height=40>Customer Number</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 192pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #d8d8d8" width=256>Customer Name</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 99pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #d8d8d8" width=132>Region</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 73pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #d8d8d8" width=97>Date Shipped</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 92pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #d8d8d8" width=122>Date Ordered</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 97pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #d8d8d8" width=129>Date Complete</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 60pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #d8d8d8" width=80>Date Due</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 167pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #d8d8d8" width=222>Manager</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 79pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: white" align=right width=105 height=16>1234</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 192pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=256>John Smith</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 99pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=132>West</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 73pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" align=right width=97>1/25/2010</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 92pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" align=right width=122>2/5/2010</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 97pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=129></TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 60pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" align=right width=80>8/30/2007</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 167pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=222>John Q</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 79pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: white" align=right width=105 height=16>1235</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 192pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white" width=256>Mary Jones</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 99pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white" width=132>East</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 73pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white" align=right width=97>2/10/2009</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 92pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white" align=right width=122>2/10/2009</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 97pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white" width=129></TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 60pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white" align=right width=80>8/14/2007</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 167pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white" width=222>Mary Smith</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl73 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12pt; BACKGROUND-COLOR: #95b3d7" height=16></TD><TD class=xl73 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #95b3d7"></TD><TD class=xl73 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #95b3d7"></TD><TD class=xl73 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #95b3d7"></TD><TD class=xl73 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #95b3d7"></TD><TD class=xl73 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #95b3d7"></TD><TD class=xl73 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #95b3d7"></TD><TD class=xl73 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #95b3d7"></TD></TR></TBODY></TABLE>


Again, this only happens when there are only 2 rows of data (not including the header row of course). When there are 3 or more rows of data, this bug never happens.

Can anyone help me out with this one?


Thanks much!

 

[VoG]

Try

If DSO.Count > 0 Then
    On Error Resume Next
    Set Blanks = Cust.SpecialCells(xlCellTypeBlanks)
    Blanks.EntireRow.Delete
    On Error GoTo 0
End If

 

[VBAProIWish]

VoG to the Rescue!

A Perfect fix as always!

Thank you thank you