B
Benjamin
I am looking for a macro to delete an entire row if a cell contains certain
words or is blank.
Thanks.
words or is blank.
Thanks.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
D
DanPtWash
Ben,
I don't know if this is the best way, but this is what I use to delete rows
which contain blank cells.
Note: where column "H" contains the blank cells I am looking for.
Columns("H:H").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete
I don't know if this is the best way, but this is what I use to delete rows
which contain blank cells.
Note: where column "H" contains the blank cells I am looking for.
Columns("H:H").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete
M
Mike H
Would you like to elaborate on which column(s) may contain the certain word
and what the certain word is?
Mike
and what the certain word is?
Mike
R
Rick Rothstein \(MVP - VB\)
If you asking your question for a single cell only (set which cell in the
Range function call)...
Sub DeleteRowBasedOnSingleCell()
With Range("D4")
If Len(.Value) = 0 Or Not IsNumeric(.Value) Then .EntireRow.Delete
End With
End Sub
If, instead, you are asking for a macro that will examine multiple cells in
a given Column, is there a column that holds data which can be used to tell
the macro when to stop deleting? Note, it cannot be the column which you are
checking for words or for being blank (otherwise ALL rows below the last
entry, down to the bottom of the grid, will be processed); so, if Column D
is the column being checked, then is (for an example) Column A containing an
index value or Names or something so the bottom of your data can be found?
Or did you just want to process selected rows of data?
Rick
Range function call)...
Sub DeleteRowBasedOnSingleCell()
With Range("D4")
If Len(.Value) = 0 Or Not IsNumeric(.Value) Then .EntireRow.Delete
End With
End Sub
If, instead, you are asking for a macro that will examine multiple cells in
a given Column, is there a column that holds data which can be used to tell
the macro when to stop deleting? Note, it cannot be the column which you are
checking for words or for being blank (otherwise ALL rows below the last
entry, down to the bottom of the grid, will be processed); so, if Column D
is the column being checked, then is (for an example) Column A containing an
index value or Names or something so the bottom of your data can be found?
Or did you just want to process selected rows of data?
Rick
B
Benjamin
The first answer from DanPtWash works when the cell is blank. How would I
adjust this to delete the row if the cell has the word "part"?
Rick - I am looking for the macro to examine multiple rows, but I don't have
an index value or name like you describe to show the end of data. What are
the downfalls of running through the entire worksheet?
Thank you both.
adjust this to delete the row if the cell has the word "part"?
Rick - I am looking for the macro to examine multiple rows, but I don't have
an index value or name like you describe to show the end of data. What are
the downfalls of running through the entire worksheet?
Thank you both.
M
Mike H
Benjamin,
Because you provide little detail you will have to change the column th is
looks in for your wword. It currently looks in the used range of column A and
deletes the entire row if it finds the word part.
Sub copyit()
Dim MyRange, MyRange1 As Range
lastrow = Cells(Rows.Count, "A").End(xlUp).Row 'change to suit
Set MyRange = Range("A1:A" & lastrow) 'Change to suit
For Each c In MyRange
If UCase(c.Value) = "PART" Then
If MyRange1 Is Nothing Then
Set MyRange1 = c.EntireRow
Else
Set MyRange1 = Union(MyRange1, c.EntireRow)
End If
End If
Next
If Not MyRange1 Is Nothing Then
MyRange1.Delete
End If
End Sub
Mike
Because you provide little detail you will have to change the column th is
looks in for your wword. It currently looks in the used range of column A and
deletes the entire row if it finds the word part.
Sub copyit()
Dim MyRange, MyRange1 As Range
lastrow = Cells(Rows.Count, "A").End(xlUp).Row 'change to suit
Set MyRange = Range("A1:A" & lastrow) 'Change to suit
For Each c In MyRange
If UCase(c.Value) = "PART" Then
If MyRange1 Is Nothing Then
Set MyRange1 = c.EntireRow
Else
Set MyRange1 = Union(MyRange1, c.EntireRow)
End If
End If
Next
If Not MyRange1 Is Nothing Then
MyRange1.Delete
End If
End Sub
Mike
R
Rick Rothstein \(MVP - VB\)
Just a couple of points regarding your post.
First, you have this declaration line in your code...
Dim MyRange, MyRange1 As Range
I just want to point out that only MyRange1 is declared as a Range
variable... MyRange is defaulted to a Variant. VB/VBA requires each variable
to be individually declared as to Type or else it gets defaulted to a
Variant.
Second, when the concept of accumulating (via the Union function) first came
up, someone (sorry, the name escapes me at the moment) pointed out there was
a limit to how many areas can be grouped like that (I think it was 8000+,
whichever power of 2 equates to that); but it was noted that before that
limit is reached, the grouping will start to bog down. Here is the code
module I came up with that accounts for the above (and also shuts off
automatic calculations and screen updating to help speed things up)...
***************** START OF CODE *****************
Sub ConditionalDelete()
Dim X As Long
Dim Z As Long
Dim LastRow As Long
Dim FoundRowToDelete As Boolean
Dim OriginalCalculationMode As Long
Dim RowsToDelete As Range
Dim SearchItems() As String
Dim DataStartRow As Long
Dim SearchColumn As String
Dim SheetName As String
' Set your search conditions here
DataStartRow = 1
SearchColumn = "B"
SheetName = "Sheet1"
' Put your search strings in the comma delimited string
SearchItems = Split("img,aboutus,othertext,etc", ",")
On Error GoTo Whoops
OriginalCalculationMode = Application.Calculation
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
With Worksheets(SheetName)
LastRow = .Cells(.Rows.Count, SearchColumn).End(xlUp).Row
For X = LastRow To DataStartRow Step -1
FoundRowToDelete = False
For Z = 0 To UBound(SearchItems)
If InStr(.Cells(X, SearchColumn).Value, SearchItems(Z)) Then
FoundRowToDelete = True
Exit For
End If
Next
If FoundRowToDelete Then
If RowsToDelete Is Nothing Then
Set RowsToDelete = .Cells(X, SearchColumn)
Else
Set RowsToDelete = Union(RowsToDelete, .Cells(X, SearchColumn))
End If
If RowsToDelete.Areas.Count > 100 Then
RowsToDelete.EntireRow.Delete
Set RowsToDelete = Nothing
End If
End If
Next
End With
If Not RowsToDelete Is Nothing Then
RowsToDelete.EntireRow.Delete
End If
Whoops:
Application.Calculation = OriginalCalculationMode
Application.ScreenUpdating = True
End Sub
***************** END OF CODE *****************
Rick
First, you have this declaration line in your code...
Dim MyRange, MyRange1 As Range
I just want to point out that only MyRange1 is declared as a Range
variable... MyRange is defaulted to a Variant. VB/VBA requires each variable
to be individually declared as to Type or else it gets defaulted to a
Variant.
Second, when the concept of accumulating (via the Union function) first came
up, someone (sorry, the name escapes me at the moment) pointed out there was
a limit to how many areas can be grouped like that (I think it was 8000+,
whichever power of 2 equates to that); but it was noted that before that
limit is reached, the grouping will start to bog down. Here is the code
module I came up with that accounts for the above (and also shuts off
automatic calculations and screen updating to help speed things up)...
***************** START OF CODE *****************
Sub ConditionalDelete()
Dim X As Long
Dim Z As Long
Dim LastRow As Long
Dim FoundRowToDelete As Boolean
Dim OriginalCalculationMode As Long
Dim RowsToDelete As Range
Dim SearchItems() As String
Dim DataStartRow As Long
Dim SearchColumn As String
Dim SheetName As String
' Set your search conditions here
DataStartRow = 1
SearchColumn = "B"
SheetName = "Sheet1"
' Put your search strings in the comma delimited string
SearchItems = Split("img,aboutus,othertext,etc", ",")
On Error GoTo Whoops
OriginalCalculationMode = Application.Calculation
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
With Worksheets(SheetName)
LastRow = .Cells(.Rows.Count, SearchColumn).End(xlUp).Row
For X = LastRow To DataStartRow Step -1
FoundRowToDelete = False
For Z = 0 To UBound(SearchItems)
If InStr(.Cells(X, SearchColumn).Value, SearchItems(Z)) Then
FoundRowToDelete = True
Exit For
End If
Next
If FoundRowToDelete Then
If RowsToDelete Is Nothing Then
Set RowsToDelete = .Cells(X, SearchColumn)
Else
Set RowsToDelete = Union(RowsToDelete, .Cells(X, SearchColumn))
End If
If RowsToDelete.Areas.Count > 100 Then
RowsToDelete.EntireRow.Delete
Set RowsToDelete = Nothing
End If
End If
Next
End With
If Not RowsToDelete Is Nothing Then
RowsToDelete.EntireRow.Delete
End If
Whoops:
Application.Calculation = OriginalCalculationMode
Application.ScreenUpdating = True
End Sub
***************** END OF CODE *****************
Rick
J
Jim Parker
Thanks Rick, this was great. I was able to also slightly revise your code to
delete all rows except the rows I wanted which were those that had a selected
character string, by reversing the "True" and "False" responses to your first
two lines of code that started with "FoundRowToDelete = "
Was at first thrown off by the end of the following statement in your code:
SearchItems = Split("img,aboutus,othertext,etc", ",") as I did not realize
the "," at the end of the statement was critical (without it all rows were
deleted in your original code). Thanks again for this great solution.
Jim Parker
delete all rows except the rows I wanted which were those that had a selected
character string, by reversing the "True" and "False" responses to your first
two lines of code that started with "FoundRowToDelete = "
Was at first thrown off by the end of the following statement in your code:
SearchItems = Split("img,aboutus,othertext,etc", ",") as I did not realize
the "," at the end of the statement was critical (without it all rows were
deleted in your original code). Thanks again for this great solution.
Jim Parker
F
Faico
Hello Rick,
Is it possible to select on the search items a value lower than, lets say 1000. that is, to delete every line with a value in those cells lower than 1000?
SearchItems = Split("<1000")
Many thanks,
Ignacio
Is it possible to select on the search items a value lower than, lets say 1000. that is, to delete every line with a value in those cells lower than 1000?
SearchItems = Split("<1000")
Many thanks,
Ignacio