I am new to VBA and I am trying to come up with a way to delete all rows (and shift cells up, if possible) where the website column cell contains the word none
. The table contains 5000+ records and this would save me a great amount of time.
I appreciate any suggestions. Many thanks in advance!
This question is related to
excel
vba
excel-2010
excel-2007
Ok I know this for VBA but if you need to do this for a once off bulk delete you can use the following Excel functionality: http://blog.contextures.com/archives/2010/06/21/fast-way-to-find-and-delete-excel-rows/ Hope this helps anyone
Example looking for the string "paper":
Try this ...
Dim r as Range
Dim x as Integer
For x = 5000 to 4 step -1 '---> or change as you want //Thanx 4 KazJaw
set r = range("E" & format(x))
if ucase(r.Value) = "NONE" then
Rows(x).EntireRow.Delete
end if
Next
Put this in a macro (IT WILL CHECK THROUGH ROW 75000, YOU CAN LOWER THE NUMBER IF YOU WOULD LIKE
Columns("E:E").Select
Selection.AutoFilter
ActiveSheet.Range("$E$1:$E$75000").AutoFilter Field:=1, Criteria1:="none"
Range("E2:E75000").SpecialCells(xlCellTypeVisible).Select
Selection.EntireRow.Delete
ActiveSheet.Cells.EntireRow.Hidden = False
ActiveSheet.Range("$E$1:$E$75000").AutoFilter Field:=1
Columns("E:E").Select
Selection.AutoFilter
Range("E2").Select
Range("A1").Select
In the "Developer Tab" go to "Visual Basic" and create a Module. Copy paste the following. Remember changing the code, depending on what you want. Then run the module.
Sub sbDelete_Rows_IF_Cell_Contains_String_Text_Value()
Dim lRow As Long
Dim iCntr As Long
lRow = 390
For iCntr = lRow To 1 Step -1
If Cells(iCntr, 5).Value = "none" Then
Rows(iCntr).Delete
End If
Next
End Sub
lRow : Put the number of the rows that the current file has.
The number "5" in the "If" is for the fifth (E) column
This was alluded to in another comment, but you could try something like this.
Sub FilterAndDelete()
Application.DisplayAlerts = False
With Sheet1 'Change this to your sheet name
.AutoFilterMode = False
.Range("A3:K3").AutoFilter
.Range("A3:K3").AutoFilter Field:=5, Criteria1:="none"
.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete
End With
Application.DisplayAlerts = True
End Sub
I haven't tested this and it is from memory, so it may require some tweaking but it should get the job done without looping through thousands of rows. You'll need to remove the 11-Jul so that UsedRange
is correct or change the offset to 2 rows instead of 1 in the .Offset(1,0)
.
Generally, before I do .Delete
I will run the macro with .Select
instead of the Delete that way I can be sure the correct range will be deleted, that may be worth doing to check to ensure the appropriate range is being deleted.
Source: Stackoverflow.com