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

AndreSoares

New Member
Joined
May 13, 2014
Messages
14
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é​
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Is this what you're looking for?

Code:
Sub Delete_Rows()
    
    Dim i As Integer
    
    With ActiveSheet.UsedRange
        For i = 2 To 24
            .AutoFilter Field:=i, Criteria1:="1"
        Next i
        .Offset(1, 0).SpecialCells(xlCellTypeVisible).Delete
        .AutoFilter
    End With
End Sub
 
Upvote 0
Try this code (this clear the rows, let me know if you want to delete it):

Code:
Sub deleteRows()

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

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

Range(Cells(1, 1), Cells(13200, 25)).AutoFilter Field:=25, Criteria1:="=23"
Range(Cells(1, 1), Cells(13200, 25)).AutoFilter Field:=1, Criteria1:="<>1"
Range(Cells(1, 1), Cells(13200, 25)).SpecialCells(xlCellTypeVisible).EntireRow.Clear

Columns(25).Delete

End Sub
 
Last edited:
Upvote 0
Try the code from my previous post on a copy of your workbook to see the results.

Good luck!
 
Upvote 0
Do you want to delete the row if the cells 2-24 all have the number 1 in them, or only if the sum of cells 2-24 = 1?
 
Upvote 0

Forum statistics

Threads
1,214,985
Messages
6,122,606
Members
449,089
Latest member
Motoracer88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top