Deleting Entire Rows based on content within a range of Columns.

[Rusman8872]

Hello . Im working with a very large spreadsheet that I am mining out a small amount of data. In Columns Q thru T I have formulas that identify the data I don't need and then returns "Delete". I need some VBA code that will look in Rows Q through T and delete the entire row of any cell that contains "Delete". The sheet will always contain between 8k and 10k rows but never more that 10k.

I would appreciate any help that someone could help with some code to drop into my macro. I have tried to use a For\Next loop but am not able to get the syntax correct.

thanks

 

[Mentor82]

Hi,
Check this out and let me know if that’s what you require.

Sub RemoveRows()
    Dim LastRow&
    Dim i&, j&
   
    LastRow = Range("A1").CurrentRegion.Rows.Count
   
    If LastRow =1 then exit Sub

    For i = LastRow to 2 step -1
        For j = 12 to 15
            If LCase(Cells(i,j))= "delete"
                Rows(i).delete
                Exit For
            End if
        Next j
    Next i

    Msgbox "Done"
End sub

 

[Rusman8872]

thanks for your reply....its giving me a compile error i just cut and pasted it at the bottom of my existing code. I posted at the bottom of my existing code should i just run it as a separate macro and call it?

 

[mse330]

This is another approach …

Sub DeleteRows()

Dim a, b
With ActiveSheet
    a = .Range("Q1", .Range("T" & .UsedRange.Rows.Count))
    ReDim b(1 To UBound(a))
    For x = 2 To UBound(a)
        Select Case "Delete"
            Case a(x, 1), a(x, 2), a(x, 3), a(x, 4)
            b(x) = "delete"
        End Select
    Next
    .[U1].Resize(UBound(b)) = Application.Transpose(b)
    .Columns("U").AutoFilter 1, "delete"
    .UsedRange.Offset(1).SpecialCells(12).EntireRow.Delete
    .UsedRange.AutoFilter
End With

End Sub

 

[Rusman8872]

Dim a, b With ActiveSheet a = .Range("Q1", .Range("T" & .UsedRange.Rows.Count)) ReDim b(1 To UBound(a)) For x = 2 To UBound(a) Select Case "Delete" Case a(x, 1), a(x, 2), a(x, 3), a(x, 4) b(x) = "delete" End Select Next .[U1].Resize(UBound(b)) = Application.Transpose(b) .Columns("U").AutoFilter 1, "delete" .UsedRange.Offset(1).SpecialCells(12).EntireRow.Delete .UsedRange.AutoFilter End With

Thanks for your reply....this one gives me a runtime error:

 

[mse330]

Do you have #N/A in your T:Q columns due to your formulas ? If yes, try to change them to anything else by using iferror() function then re-test the code again

 

[Rusman8872]

Do you have #N/A in your T:Q columns due to your formulas ? If yes, try to change them to anything else by using iferror() function then re-test the code again

That was the problem once i handled the #NA it worked perfectly....thanks you so much

 

[mse330]

Glad to help & thanks for reporting back