Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to delete row if
I am looking for a macro to delete an entire row if a cell contains certain
words or is blank. Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to delete row if
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 "Benjamin" wrote: I am looking for a macro to delete an entire row if a cell contains certain words or is blank. Thanks. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to delete row if
Would you like to elaborate on which column(s) may contain the certain word
and what the certain word is? Mike "Benjamin" wrote: I am looking for a macro to delete an entire row if a cell contains certain words or is blank. Thanks. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to delete row if
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 "Benjamin" wrote in message ... I am looking for a macro to delete an entire row if a cell contains certain words or is blank. Thanks. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to delete row if
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. "Rick Rothstein (MVP - VB)" wrote: 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 "Benjamin" wrote in message ... I am looking for a macro to delete an entire row if a cell contains certain words or is blank. Thanks. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to delete row if
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 "Benjamin" wrote: 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. "Rick Rothstein (MVP - VB)" wrote: 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 "Benjamin" wrote in message ... I am looking for a macro to delete an entire row if a cell contains certain words or is blank. Thanks. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to delete row if
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 "Mike H" wrote in message ... 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 "Benjamin" wrote: 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. "Rick Rothstein (MVP - VB)" wrote: 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 "Benjamin" wrote in message ... I am looking for a macro to delete an entire row if a cell contains certain words or is blank. Thanks. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to delete row if
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 "Rick Rothstein (MVP - VB)" wrote: 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 "Mike H" wrote in message ... 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 "Benjamin" wrote: 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. "Rick Rothstein (MVP - VB)" wrote: 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 "Benjamin" wrote in message ... I am looking for a macro to delete an entire row if a cell contains certain words or is blank. Thanks. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to delete row if
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
delete macro | Excel Discussion (Misc queries) | |||
delete a macro that isn't in macro list | Excel Discussion (Misc queries) | |||
How can I delete a macro when the Delete button is not active? | Excel Worksheet Functions | |||
delete all macro | Charts and Charting in Excel | |||
How do i delete a macro in Excel 2003 when delete isn't highlight | Excel Discussion (Misc queries) |