Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting specific rows with a specific criteria using inputbox
Hi All,
I am trying to build a macro which would establish a range and criteria to selec certain rows to delete. I have it partially working, but cant get past this point. Can someone please show me the error of my ways! The code is listed below, and I am sure I do not need most of it. greatly appreciate any assistance. Sub DeleteRows() Dim r As Integer Dim totalR As Integer Dim question1 As String Dim question2 As String Dim question3 As String Dim mySearch As String Dim cell3 As Variant Dim BadWord As Range Dim cell1 As Range Dim cell2 As Range Dim cell4 As Range Dim allCells As Range totalR = Selection.Rows.Count question1 = "What cell would you like to start with?" question2 = "What what cell would you like to end with?" question3 = "Please enter search word." mySearch = cell3 Set cell1 = Application.InputBox(prompt:=question1, _ Title:="Range to Search", Type:=8) Set cell2 = Application.InputBox(prompt:=question2, _ Title:="Range to Search", Type:=8) cell1.Value = cell1.Value cell3 = InputBox(prompt:=question3, _ Title:="Search word or phrase") Set allCells = Range(cell1, cell2) allCells.Value = allCells.Value For Each cell1 In allCells If allCells.Value < cell3.Value Then ActiveCell.EntireRow.Select Selection.EntireRow.Delete End If Next end sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting specific rows with a specific criteria using inputbox
First, you're asking for a word, but deleting the rows that don't match that
word with this line: If allCells.Value < cell3.Value Then Did you really want to delete the cells that match that word? If allCells.Value = cell3.Value Then Anyway... This asks the user once for the range to search and builds a giant range based on the cells that should be deleted. Then deletes the rows all at once. Option Explicit Sub DeleteRows2() Dim r As Long Dim RngToSearch As Range Dim DelRng As Range Dim WordToLookFor As String Dim myCell As Range Set RngToSearch = Nothing On Error Resume Next Set RngToSearch = Application.InputBox _ (Prompt:="select the complete range to search", Type:=8) On Error GoTo 0 If RngToSearch Is Nothing Then Beep Exit Sub 'user hit cancel End If WordToLookFor = InputBox(Prompt:="Please enter search word.", _ Title:="Search word or phrase") For Each myCell In RngToSearch.Cells If LCase(myCell.Value) < LCase(WordToLookFor) Then If DelRng Is Nothing Then Set DelRng = myCell Else Set DelRng = Union(myCell, DelRng) End If End If Next myCell If DelRng Is Nothing Then MsgBox "No cells found, nothing deleted!" Else Set DelRng = Intersect(DelRng.EntireRow, DelRng.Parent.Columns(1)) DelRng.EntireRow.Delete End If End Sub You may want: If LCase(myCell.Value) < LCase(WordToLookFor) Then to be: If LCase(myCell.Value) = LCase(WordToLookFor) Then And you may want this: Set RngToSearch = Application.InputBox _ (Prompt:="select the complete range to search", Type:=8) to be: Set RngToSearch = Application.InputBox _ (Prompt:="select the complete range to search", Type:=8) _ .areas(1).columns(1) if the user is supposed to select a single column range. Greg wrote: Hi All, I am trying to build a macro which would establish a range and criteria to selec certain rows to delete. I have it partially working, but cant get past this point. Can someone please show me the error of my ways! The code is listed below, and I am sure I do not need most of it. greatly appreciate any assistance. Sub DeleteRows() Dim r As Integer Dim totalR As Integer Dim question1 As String Dim question2 As String Dim question3 As String Dim mySearch As String Dim cell3 As Variant Dim BadWord As Range Dim cell1 As Range Dim cell2 As Range Dim cell4 As Range Dim allCells As Range totalR = Selection.Rows.Count question1 = "What cell would you like to start with?" question2 = "What what cell would you like to end with?" question3 = "Please enter search word." mySearch = cell3 Set cell1 = Application.InputBox(prompt:=question1, _ Title:="Range to Search", Type:=8) Set cell2 = Application.InputBox(prompt:=question2, _ Title:="Range to Search", Type:=8) cell1.Value = cell1.Value cell3 = InputBox(prompt:=question3, _ Title:="Search word or phrase") Set allCells = Range(cell1, cell2) allCells.Value = allCells.Value For Each cell1 In allCells If allCells.Value < cell3.Value Then ActiveCell.EntireRow.Select Selection.EntireRow.Delete End If Next end sub -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting specific rows with a specific criteria using inputbox
Thanks Dave, this is very helpful!
"Dave Peterson" wrote: First, you're asking for a word, but deleting the rows that don't match that word with this line: If allCells.Value < cell3.Value Then Did you really want to delete the cells that match that word? If allCells.Value = cell3.Value Then Anyway... This asks the user once for the range to search and builds a giant range based on the cells that should be deleted. Then deletes the rows all at once. Option Explicit Sub DeleteRows2() Dim r As Long Dim RngToSearch As Range Dim DelRng As Range Dim WordToLookFor As String Dim myCell As Range Set RngToSearch = Nothing On Error Resume Next Set RngToSearch = Application.InputBox _ (Prompt:="select the complete range to search", Type:=8) On Error GoTo 0 If RngToSearch Is Nothing Then Beep Exit Sub 'user hit cancel End If WordToLookFor = InputBox(Prompt:="Please enter search word.", _ Title:="Search word or phrase") For Each myCell In RngToSearch.Cells If LCase(myCell.Value) < LCase(WordToLookFor) Then If DelRng Is Nothing Then Set DelRng = myCell Else Set DelRng = Union(myCell, DelRng) End If End If Next myCell If DelRng Is Nothing Then MsgBox "No cells found, nothing deleted!" Else Set DelRng = Intersect(DelRng.EntireRow, DelRng.Parent.Columns(1)) DelRng.EntireRow.Delete End If End Sub You may want: If LCase(myCell.Value) < LCase(WordToLookFor) Then to be: If LCase(myCell.Value) = LCase(WordToLookFor) Then And you may want this: Set RngToSearch = Application.InputBox _ (Prompt:="select the complete range to search", Type:=8) to be: Set RngToSearch = Application.InputBox _ (Prompt:="select the complete range to search", Type:=8) _ .areas(1).columns(1) if the user is supposed to select a single column range. Greg wrote: Hi All, I am trying to build a macro which would establish a range and criteria to selec certain rows to delete. I have it partially working, but cant get past this point. Can someone please show me the error of my ways! The code is listed below, and I am sure I do not need most of it. greatly appreciate any assistance. Sub DeleteRows() Dim r As Integer Dim totalR As Integer Dim question1 As String Dim question2 As String Dim question3 As String Dim mySearch As String Dim cell3 As Variant Dim BadWord As Range Dim cell1 As Range Dim cell2 As Range Dim cell4 As Range Dim allCells As Range totalR = Selection.Rows.Count question1 = "What cell would you like to start with?" question2 = "What what cell would you like to end with?" question3 = "Please enter search word." mySearch = cell3 Set cell1 = Application.InputBox(prompt:=question1, _ Title:="Range to Search", Type:=8) Set cell2 = Application.InputBox(prompt:=question2, _ Title:="Range to Search", Type:=8) cell1.Value = cell1.Value cell3 = InputBox(prompt:=question3, _ Title:="Search word or phrase") Set allCells = Range(cell1, cell2) allCells.Value = allCells.Value For Each cell1 In allCells If allCells.Value < cell3.Value Then ActiveCell.EntireRow.Select Selection.EntireRow.Delete End If Next end sub -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting Rows w/ Specific Criteria | Excel Discussion (Misc queries) | |||
Deleting rows after a specific Value | Excel Programming | |||
Deleting Specific Rows | Excel Discussion (Misc queries) | |||
Deleting specific rows | Excel Programming | |||
Deleting rows with specific value from row 1 to 200 | Excel Programming |