Deleting Rows if Row (But 1st Cell) sums to 1

[AndreSoares]

Hey,

I have a worksheet (13200 rows x 24 columns) and need to clean up the rows in which all cells but the first one are equal to 1.

Any help is more than welcome, thank you so much in advance!

Best regards
André​

 

[AndreSoares]

Basically if any of the cells 2-24 (B-AA) have a 1 wherever, in between zeros.

 

[skorpionkz]

try now this will not delete header (Assuming it is in row 1)

Sub deleteRows()

Cells(2, 25).FormulaR1C1 = "=COUNTIF(RC[-23]:RC[-1],1)"
Cells(2, 25).AutoFill Destination:=Range("Y2:Y13200")

With Range(Cells(2, 25), Cells(13200, 25))
    .Copy
    .PasteSpecial Paste:=xlPasteValues
End With

Range(Cells(2, 1), Cells(13200, 25)).AutoFilter Field:=25, Criteria1:=">1"
Range(Cells(2, 1), Cells(13200, 25)).SpecialCells(xlCellTypeVisible).EntireRow.Delete

Columns(25).Delete

End Sub

 

[AndreSoares]

Hey again!
This one deletes row 2 and not any of the rows I want...

 

[skorpionkz]

yeah:/

I was trying to edit the post but to late

Sub deleteRows()

Cells(1, 25).FormulaR1C1 = "=COUNTIF(RC[-23]:RC[-1],1)"
Cells(1, 25).AutoFill Destination:=Range("Y1:Y13200")

With Range(Cells(2, 25), Cells(13200, 25))
    .Copy
    .PasteSpecial Paste:=xlPasteValues
End With

Range(Cells(1, 1), Cells(13200, 25)).AutoFilter Field:=25, Criteria1:=">=1"
On Error Resume Next
Range(Cells(2, 1), Cells(13200, 25)).SpecialCells(xlCellTypeVisible).EntireRow.Delete
On Error GoTo 0

Columns(25).Delete

End Sub

 

[AndreSoares]

Ok, I see some results, but some rows still sum 1...
Even though it didn't totally work, I got from 13k to 3k rows! Can you trim this macro any better?
Thanks so much!

 

[skorpionkz]

it should remove all rows where "1" exists in columns between 2 and 24. I used count function not sum. can you give a sample data where the rows was not removed

 

[AndreSoares]

Hey again,
Here is a sample:

45

0

0

0

0

0

0

0

0

0

0

0

0

0

0

0

0

0

0

0

0

0

0

0

1

0

<tbody>
</tbody>

Also, I noticed the Y column disappeared after using the macro... :/

 

[skorpionkz]

Yes Column Y is 25th column (as per #1 macro need to check columns 2-24). The macro add formula there and then delete this column. If you need to check column B-AA it is 2-27. this is a reason why some rows are not deleted as Macro check 2-24 (B-X)

Check this code. It will check columns B to AA

Sub deleteRows()

Cells(1, 100).FormulaR1C1 = "=COUNTIF(RC[-98]:RC[-1],1)"
Cells(1, 100).AutoFill Destination:=Range(Cells(1, 100), Cells(13200, 100))

With Range(Cells(2, 100), Cells(13200, 100))
    .Copy
    .PasteSpecial Paste:=xlPasteValues
End With

Range(Cells(1, 1), Cells(13200, 100)).AutoFilter Field:=100, Criteria1:=">=1"
On Error Resume Next
Range(Cells(2, 1), Cells(13200, 100)).SpecialCells(xlCellTypeVisible).EntireRow.Delete
On Error GoTo 0

Range(Cells(1, 1), Cells(13200, 100)).AutoFilter
Columns(100).Delete

End Sub

 

[AndreSoares]

PERFECT :D Thanks so much!

 

[skorpionkz]

You are welcome!