Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search for sepcific words and delete rows between them
This question must have been answered before, but I am not able to figure out
the cause to generate the error. sometimes it is working but sometimes it is giving error. I am trying to run a macro, to search for two specific words and delete entire rows between these words, including them. These searching words are always in column 1. I am using the following code: but it is giving runtime error 91 (Object variable or with block variable not set). How can I correct this. Thank you so much in advance for any help. ############# Public Sub Auto_DeleteRows() Dim text1Row , text2Row As Long Dim WS As Worksheet For Each WS In ActiveWorkbook.Worksheets With WS .Activate Set text1Row = Cells.Find("text1:", [A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious) Set text2Row = Cells.Find("text2:", [A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious) ' delete all rows between text1 and text2: Range(Cells(text1Row.Row - 2, 1), Cells(text2Row.Row, 1)).EntireRow.Delete End With Next WS End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search for sepcific words and delete rows between them
Try the below..(There is no error handling)
Public Sub Auto_DeleteRows() Dim text1Row, text2Row As Long Dim WS As Worksheet For Each WS In ActiveWorkbook.Worksheets text1Row = WS.Columns(1).Find("text1:", Range("A1"), _ SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row text2Row = WS.Columns(1).Find("text2:", Range("A1"), _ SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row WS.Rows(text1Row & ":" & text2Row).Delete Next WS End Sub If this post helps click Yes --------------- Jacob Skaria "Sheela" wrote: This question must have been answered before, but I am not able to figure out the cause to generate the error. sometimes it is working but sometimes it is giving error. I am trying to run a macro, to search for two specific words and delete entire rows between these words, including them. These searching words are always in column 1. I am using the following code: but it is giving runtime error 91 (Object variable or with block variable not set). How can I correct this. Thank you so much in advance for any help. ############# Public Sub Auto_DeleteRows() Dim text1Row , text2Row As Long Dim WS As Worksheet For Each WS In ActiveWorkbook.Worksheets With WS .Activate Set text1Row = Cells.Find("text1:", [A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious) Set text2Row = Cells.Find("text2:", [A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious) ' delete all rows between text1 and text2: Range(Cells(text1Row.Row - 2, 1), Cells(text2Row.Row, 1)).EntireRow.Delete End With Next WS End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search for sepcific words and delete rows between them
I would code this the below way..
Public Sub Auto_DeleteRows1() Dim rngFind1 As Range, rngFind2 As Range, ws As Worksheet For Each ws In ActiveWorkbook.Worksheets Set rngFind1 = ws.Columns(1).Find("text1:", Range("A1"), _ SearchOrder:=xlByRows, SearchDirection:=xlPrevious) Set rngFind2 = ws.Columns(1).Find("text2:", Range("A1"), _ SearchOrder:=xlByRows, SearchDirection:=xlPrevious) If Not (rngFind1 Is Nothing) And Not (rngFind2 Is Nothing) Then _ ws.Rows(rngFind1.Row & ":" & rngFind2.Row).Delete Next End Sub If this post helps click Yes --------------- Jacob Skaria "Sheela" wrote: This question must have been answered before, but I am not able to figure out the cause to generate the error. sometimes it is working but sometimes it is giving error. I am trying to run a macro, to search for two specific words and delete entire rows between these words, including them. These searching words are always in column 1. I am using the following code: but it is giving runtime error 91 (Object variable or with block variable not set). How can I correct this. Thank you so much in advance for any help. ############# Public Sub Auto_DeleteRows() Dim text1Row , text2Row As Long Dim WS As Worksheet For Each WS In ActiveWorkbook.Worksheets With WS .Activate Set text1Row = Cells.Find("text1:", [A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious) Set text2Row = Cells.Find("text2:", [A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious) ' delete all rows between text1 and text2: Range(Cells(text1Row.Row - 2, 1), Cells(text2Row.Row, 1)).EntireRow.Delete End With Next WS End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search for sepcific words and delete rows between them
Sub dural()
' put the following in the Worksheet loop Dim text1Row As Range, text2Row As Range Set text1Row = Cells.Find("text1:", [A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious) Set text2Row = Cells.Find("text2:", [A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious) Dim s As String s = text1Row.Row & ":" & text2Row.Row Rows(s).Delete End Sub -- Gary''s Student - gsnu200906 "Sheela" wrote: This question must have been answered before, but I am not able to figure out the cause to generate the error. sometimes it is working but sometimes it is giving error. I am trying to run a macro, to search for two specific words and delete entire rows between these words, including them. These searching words are always in column 1. I am using the following code: but it is giving runtime error 91 (Object variable or with block variable not set). How can I correct this. Thank you so much in advance for any help. ############# Public Sub Auto_DeleteRows() Dim text1Row , text2Row As Long Dim WS As Worksheet For Each WS In ActiveWorkbook.Worksheets With WS .Activate Set text1Row = Cells.Find("text1:", [A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious) Set text2Row = Cells.Find("text2:", [A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious) ' delete all rows between text1 and text2: Range(Cells(text1Row.Row - 2, 1), Cells(text2Row.Row, 1)).EntireRow.Delete End With Next WS End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search for sepcific words and delete rows between them
It worked. Thank you so much to both of you for quick responses. "Jacob Skaria" wrote: I would code this the below way.. Public Sub Auto_DeleteRows1() Dim rngFind1 As Range, rngFind2 As Range, ws As Worksheet For Each ws In ActiveWorkbook.Worksheets Set rngFind1 = ws.Columns(1).Find("text1:", Range("A1"), _ SearchOrder:=xlByRows, SearchDirection:=xlPrevious) Set rngFind2 = ws.Columns(1).Find("text2:", Range("A1"), _ SearchOrder:=xlByRows, SearchDirection:=xlPrevious) If Not (rngFind1 Is Nothing) And Not (rngFind2 Is Nothing) Then _ ws.Rows(rngFind1.Row & ":" & rngFind2.Row).Delete Next End Sub If this post helps click Yes --------------- Jacob Skaria "Sheela" wrote: This question must have been answered before, but I am not able to figure out the cause to generate the error. sometimes it is working but sometimes it is giving error. I am trying to run a macro, to search for two specific words and delete entire rows between these words, including them. These searching words are always in column 1. I am using the following code: but it is giving runtime error 91 (Object variable or with block variable not set). How can I correct this. Thank you so much in advance for any help. ############# Public Sub Auto_DeleteRows() Dim text1Row , text2Row As Long Dim WS As Worksheet For Each WS In ActiveWorkbook.Worksheets With WS .Activate Set text1Row = Cells.Find("text1:", [A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious) Set text2Row = Cells.Find("text2:", [A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious) ' delete all rows between text1 and text2: Range(Cells(text1Row.Row - 2, 1), Cells(text2Row.Row, 1)).EntireRow.Delete End With Next WS End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search for sepcific words and delete rows between them
I have a couple of questions for clarification (with an eye toward error
handling)... Will "text1" ALWAYS appear at a lower row number than "text2"? If not, and "text2" occurs before "text1", do you still want perform your deletion? Can "text1" and/or "text2" appear more than once in the list? If so, which ones do you want to use... the lower numbered row where "text1" appears and the higher numbered row where "text2" appears, or some other way? -- Rick (MVP - Excel) "Sheela" wrote in message ... This question must have been answered before, but I am not able to figure out the cause to generate the error. sometimes it is working but sometimes it is giving error. I am trying to run a macro, to search for two specific words and delete entire rows between these words, including them. These searching words are always in column 1. I am using the following code: but it is giving runtime error 91 (Object variable or with block variable not set). How can I correct this. Thank you so much in advance for any help. ############# Public Sub Auto_DeleteRows() Dim text1Row , text2Row As Long Dim WS As Worksheet For Each WS In ActiveWorkbook.Worksheets With WS .Activate Set text1Row = Cells.Find("text1:", [A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious) Set text2Row = Cells.Find("text2:", [A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious) ' delete all rows between text1 and text2: Range(Cells(text1Row.Row - 2, 1), Cells(text2Row.Row, 1)).EntireRow.Delete End With Next WS End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search for sepcific words and delete rows between them
Thank you for your concern. The "text1" always appears before "text2". And it can also comes with other words after text2. I realized this after posting my question and I changed the search criteria for text1 such that the direction should be xlnext. Text2 comes only once in a worksheet. Thank you for the heads up. "Rick Rothstein" wrote: I have a couple of questions for clarification (with an eye toward error handling)... Will "text1" ALWAYS appear at a lower row number than "text2"? If not, and "text2" occurs before "text1", do you still want perform your deletion? Can "text1" and/or "text2" appear more than once in the list? If so, which ones do you want to use... the lower numbered row where "text1" appears and the higher numbered row where "text2" appears, or some other way? -- Rick (MVP - Excel) "Sheela" wrote in message ... This question must have been answered before, but I am not able to figure out the cause to generate the error. sometimes it is working but sometimes it is giving error. I am trying to run a macro, to search for two specific words and delete entire rows between these words, including them. These searching words are always in column 1. I am using the following code: but it is giving runtime error 91 (Object variable or with block variable not set). How can I correct this. Thank you so much in advance for any help. ############# Public Sub Auto_DeleteRows() Dim text1Row , text2Row As Long Dim WS As Worksheet For Each WS In ActiveWorkbook.Worksheets With WS .Activate Set text1Row = Cells.Find("text1:", [A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious) Set text2Row = Cells.Find("text2:", [A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious) ' delete all rows between text1 and text2: Range(Cells(text1Row.Row - 2, 1), Cells(text2Row.Row, 1)).EntireRow.Delete End With Next WS End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete Rows Based on Words | Excel Discussion (Misc queries) | |||
Macro to search across multiple columns and delete rows not containing specific value | Excel Programming | |||
Besides manually how can I delete words in multiple rows? | Excel Discussion (Misc queries) | |||
Search for the word "continued", if found, delete that row + 10 rows above | Excel Programming | |||
Search for the word "continued", if found, delete that row + 10 rows above | Excel Programming |