Find A Word, Delete That Row And The Next 9

Is there any specific criteria based on which the next 9 rows should be deleted? What if they dont contain the word "UNUSED"? How will the code obtain the word to be searched? Should the user be asked for it? You can also attach a sample workbook so we can better understand your issue.

Hi,

Please Login or Register  to view this content.

Steffen Thomsen

Thanks for the replys,

arlu1201,

The sheet is being used to produce large configurations for routers. It is a standard template that gets data from multiple spreadshhets using VLOOKUPs. Not all enrties are used so when it's complete there are a lot of sections that need to be deleted. The sections to be deleted will have the word "UNUSED" in the cell which is first line of the section. This row and the following 9 need to be deleted. The 9 rows following will not have the word UNUSED in them. There will be up to 200 sections that need to be deleted.

Steffen,

I've tried the code but get the error message "Object doesn't support this property or method" for the line "c = .Find(What:=findStr, SearchDirection:=xlNext, LookIn:=xlFormulas).Row".

As I said earlier I'm new to this so maybe I'm doing something wrong which would be obvious to someone who understood this properly.

Thanks

Keith

Sorry, missed something

Please Login or Register  to view this content.

Now I get:-

Run-time error "1004"
Application-defined or object-defined error

for the line:-

Sheets(1).Rows(c, c + 9).Delete

Any ideas please?

Keith

Can you post an example workbook, and i will look at it.

Steffen Thomsen

Steffen,

Please see attached file Example Data. This is a very small portion of the sheet that I need to delete rows from but will show you what I'm lookin for.

Thanks

Keith

Hi,

Try this.

Open the file and run the macro, test it a couple of times with different lenghts of data and see if it does whats required.

Steffen Thomsen

snb, as allways with a more efficient solution

hi,

the following script works good untill #NA is not in the row

Sub undodelete()
Dim i, j, k As Variant

i = 0
j = 0
k = 0


For i = 1 To 500

If Cells(i, 1).Value = "UNUSED - DELETE" Then
j = i + 8
k = i

For l = k To k + 8

Rows(k).Delete Shift:=xlUp

Next

End If
Next


End Sub

hello,

here is the solution again with #N/A problem fixed.

Sub undodelete()
Dim i, j, k, l As Variant
i = 0
j = 0
k = 0
l = 0
For i = 1 To 52

For l = i To 52

If Cells(l, 1).Text = "#N/A" Then
Cells(l, 1).Select
i = l + 1
Else

If Cells(i, 1).Value = "UNUSED - DELETE" Then
Cells(i, 1).Select
k = i + 8
For j = i To k

Rows(j).Delete

Next


Else
Exit For

End If


End If

Next

Next
End Sub

@shaukat

Nice off you to tip in with a solution, but still snb's is by far the fastest and easiest to read solution

Thanks to everyone who has contributed.

Steffen, snb,

Thank you both very much. Both your codes do exactly what I needed and will save me a lot of time. I even think that I'm starting to understand some of it! Is that dangerous?

Now what I hope is a simple question. I actually need to look for 2 different text strings and delete 2 different quantities of rows. (UNUSED delete 9 rows, SPARE delete 7 rows) I've modified both sets of your codes to do what I need separately so I've now got 4 macros ( 2 from each of your solutions) which I can run as seperate macros which works fine, but,it would be nice to combine two together for neatness. I'm guessing that it's quite simple but as I said at the beginning, I'm new to this.

Thanks

Keith

Hi,

Just a slight modification of snb's code
Sure he can find a better way but here you are

Please Login or Register  to view this content.

Hi snb,

Nice. Like the syntax.

Though both need to look threw the column twice, and both uses excel native functions,
wich ones fastest?

Sorry!

Im not native english, so wasn't aware

Steffen,

I've tried your suggestion and it only runs the first half of the code. The second part appears to be ignored?

snb,

I can see what you're dong with your code but it won't do what I need because for UNUSED in need to delete the next 9 rows and for SPARE the next 7?

Regards

Keith

Have you run snb's code?

I does whats required!

The replace, especially "Unused2" is important

Hi,

snb's code does work but as I said before it won't do what I need because for UNUSED I need to delete the next 9 rows, and for SPARE the next 7 rows. However what I have done is written another macro that calls the other 2 so I've now got exactly what I wanted. I'm very pleased with the result and thanks again for all the help.

Best regards

Keith