VBA adding blank lines when range copied

[Afro_Cookie]

I had some help with some code from this thread ( Macro crashes with button, works perfectly if run line by line ) and have modified it slightly since then, see below.

When I run it, it duplicates the number of lines I have but makes them blank. I added a special cells portion to delete the data in a column with no data, but it does not seem to be working.

Can someone please help me delete the blank rows that are added or identify why they are being added?

Sub S0rt()

Dim tbl As Range
Set tbl = Sheets("WSG0106_CON10170").AutoFilter.Range
Set tbl = tbl.Resize(tbl.Rows.Count - 1)
Set tbl = tbl.Offset(1)

    Sheets("WSG0106_CON10170").Select
    Range("B:C, E:E").ClearContents
    Range("A:A").Delete
    Range("AC:AC").Delete
    Columns(5).Insert
    
    tbl.Copy Sheets("Table").Range("A" & Cells.Rows.Count).End(xlUp).Offset(1, 0)
       
    ActiveWorkbook.Queries("WSG0106_CON10170").Delete
    Sheets("WSG0106_CON10170").Delete
    
    On Error Resume Next
    Sheets("Table").Select
    Application.ScreenUpdating = False
    Columns("C:C").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    Application.ScreenUpdating = True
    
End Sub

 

[johnnyL]

If you want to delete an entire row if a cell in column C is blank then:

Columns("C").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

 

[Fluff]

How about

   Application.ScreenUpdating = False
   With Sheets("Table")
      With .Range("C1", .Range("C" & Rows.Count).End(xlUp))
         .Value = .Value
         On Error Resume Next
         .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
         On Error GoTo 0
      End With
   End With
   Application.ScreenUpdating = True

 

[Afro_Cookie]

@Fluff @johnnyL
Thank you both for your replies! Muchly appreciated.

Both options work in test and now I'm curious, would one code be more favourable over the other?

 

[Fluff]

If your code didn't work, then I don't see how the change suggested by JohnnyL could make any difference.
However if they both work, use which ever you prefer.

 

[Afro_Cookie]

I did further testing with actual data and the error still occurred. After adding in Fluff's modifications it resolved itself. Thanks @Fluff

Below is the final code I'm using.

Sub Foot()

Dim tbl As Range
Set tbl = Sheets("WSG0106_CON10170").AutoFilter.Range
Set tbl = tbl.Resize(tbl.Rows.Count - 1)
Set tbl = tbl.Offset(1)

    Sheets("WSG0106_CON10170").Select
    Range("B:C, E:E").ClearContents
    Range("A:A").Delete
    Range("AC:AC").Delete
    Columns(5).Insert
    
    tbl.Copy Sheets("Table").Range("A" & Cells.Rows.Count).End(xlUp).Offset(1, 0)
       
    ActiveWorkbook.Queries("WSG0106_CON10170").Delete
    Sheets("WSG0106_CON10170").Delete
  
' Thanks Fluff for the updated error checking code.  
   Application.ScreenUpdating = False
   With Sheets("Table")
      With .Range("Y4", .Range("Y" & Rows.Count).End(xlUp))
         .Value = .Value
         On Error Resume Next
         .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
         On Error GoTo 0
      End With
   End With
   Application.ScreenUpdating = True
    
End Sub

 

[Fluff]

Glad we could help & thanks for the feedback.