Code to delete entire rows if zero in a particular column (after pasting data in a new tab)

[AidanDanby]

Hi All,
Please can someone give me some code that will do the following:

1) Paste all data from Sheet1 into a new sheet.
2) Delete entire rows if there is a zero in column A

Cheers,

Aidan

 

[vcoolio]

Hello Aidan,

I dare say that we are going to need more information, however try the following as a starting point:-

Sub Test()

    Application.ScreenUpdating = False
    
    Sheet1.UsedRange.Copy Sheet2.[A1]
    
    With Sheet2.[A1].CurrentRegion
            .AutoFilter 1, 0
            .Offset(1).EntireRow.Delete
            .AutoFilter
    End With
    
    Application.ScreenUpdating = True

End Sub

I'm assuming that your data starts in Sheet1 Row 2 with headings in Row 1.

I hope that this helps.

Cheerio,
vcoolio.

 

[Peter_SSs]

If your data is very large with lots of disjoint rows to delete, then this will be considerably faster. If the data is not very large then no particular benefit with this code.
I am also assuming a header row.

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Sub Test1()
  Dim a As Variant, b As Variant
  Dim nc As Long, i As Long, k As Long
 
  Sheets("Sheet1").Copy After:=Sheets("Sheet1")
  nc = Cells.Find(What:="*", LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
  a = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To UBound(a), 1 To 1)
  For i = 1 To UBound(a)
    If a(i, 1) = 0 Then
      b(i, 1) = 1
      k = k + 1
    End If
  Next i
  If k > 0 Then
    Application.ScreenUpdating = False
    With Range("A2").Resize(UBound(a), nc)
      .Columns(nc).Value = b
      .Sort Key1:=.Columns(nc), Order1:=xlAscending, Header:=xlNo
      .Resize(k).EntireRow.Delete
    End With
    Application.ScreenUpdating = True
  End If
End Sub

 

[AidanDanby]

Hi Vcoolio - thank you for your reply.
When I try and run the code it states 'Object Required' ?

 

[Peter_SSs]

When I try and run the code it states 'Object Required' ?

When you report a vba error, please also report the line of code that produced the error.

@vcoolio's code worked for me.

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[AidanDanby]

If your data is very large with lots of disjoint rows to delete, then this will be considerably faster. If the data is not very large then no particular benefit with this code.
I am also assuming a header row.

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Sub Test1()
  Dim a As Variant, b As Variant
  Dim nc As Long, i As Long, k As Long
 
  Sheets("Sheet1").Copy After:=Sheets("Sheet1")
  nc = Cells.Find(What:="*", LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
  a = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To UBound(a), 1 To 1)
  For i = 1 To UBound(a)
    If a(i, 1) = 0 Then
      b(i, 1) = 1
      k = k + 1
    End If
  Next i
  If k > 0 Then
    Application.ScreenUpdating = False
    With Range("A2").Resize(UBound(a), nc)
      .Columns(nc).Value = b
      .Sort Key1:=.Columns(nc), Order1:=xlAscending, Header:=xlNo
      .Resize(k).EntireRow.Delete
    End With
    Application.ScreenUpdating = True
  End If
End Sub

Hi Peter - I am getting a Run-time error '1004': Application-defined or object-defined error when I try and run the code?

 

[vcoolio]

Hello Aidan,

In this case I can't say what would cause the error without seeing a sample of your worksheet(s). I'm thinking that your data starts further down the sheet rather than at Row 1.

I'd suggest trying Peter's code first just to see if the same error comes up. I used Peter's code on a dummy test sheet and it worked flawlessly.

Cheerio.
vcoolio.

 

[AidanDanby]

When you report a vba error, please also report the line of code that produced the error.

@vcoolio's code worked for me.

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How do I dee the line of code that producers the error?

 

[Peter_SSs]

How do I dee the line of code that producers the error?

Do you get the option of a 'Debug' button? If so, click that and the offending line should be highlighted yellow. Please report the full error message and identify the code line.
Also make sure it is clear whose code you are referring to.

 

[AidanDanby]

Do you get the option of a 'Debug' button? If so, click that and the offending line should be highlighted yellow. Please report the full error message and identify the code line.
Also make sure it is clear whose code you are referring to.