I have only one column of data. I need to write a macro that would go through all the values and delete all rows that contain the word "paper".
A B
1 678
2 paper
3 3
4 09
5 89
6 paper
The problem is that the number of rows is not fixed. Sheets may have different number of rows.
If you're confident that the rows in question would always contain "paper" specifically and never any other string, you should match based on the value paper rather than it being a string. This is because, particularly in Excel, sometimes you may have numbers stored as strings without realizing it--and you don't want to delete those rows.
Sub DeleteRowsWithPaper()
Dim a As Integer
a = 1
Do While Cells(a, 1) <> ""
If Cells(a, 1) = "paper" Then
Rows(a).Delete Shift:=xlUp
'Row counter should not be incremented if row was just deleted
Else
'Increment a for next row only if row not deleted
a = a + 1
End If
Loop
End Sub
Here is another simple macro that will remove all rows with non-numeric values in column A (besides row 1).
Sub DeleteRowsWithStringsInColumnA()
Dim i As Long
With ActiveSheet '<~~ Or whatever sheet you may want to use the code for
For i = .Cells(.Cells(.Rows.Count, 1).End(xlUp).Row, 1).Row To 2 Step -1 '<~~ To row 2 keeps the header
If IsNumeric(.Cells(i, 1).Value) = False Then .Cells(i, 1).EntireRow.Delete
Next i
End With
End Sub
The following is a flexible macro that allows you to input a string or number to find and delete its respective row. It is able to process 1.04 million rows of simple strings and numbers in 2.7 seconds.
Sub DeleteRows()
Dim Wsht As Worksheet
Dim LRow, Iter As Long
Dim Var As Variant
Var = InputBox("Please specify value to find and delete.")
Set Wsht = ThisWorkbook.ActiveSheet
LRow = Wsht.Cells(Rows.Count, 1).End(xlUp).Row
StartTime = Timer
Application.ScreenUpdating = False
With Wsht
For Iter = LRow To 1 Step -1
If InStr(.Cells(Iter, 1), Var) > 0 Then
.Cells(Iter, 1).EntireRow.Delete
End If
Next Iter
End With
Application.ScreenUpdating = True
Debug.Print Timer - StartTime
End Sub
