find word cell and

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Find a word in a cell and delete certain rows bellow

Find a word in a cell and delete certain rows bellow

resolvedResolved · Urgent Priority · Version 2007

Carmen has attended:
Excel VBA Intro Intermediate course

Find a word in a cell and delete certain rows bellow

I am working in a macro that finds a specific word (Subtotal) in a cell and delete the entire row. But I need to delete as well the next consecutive 6 rows all along the same column range B.

What I have done so far works well but it just deletes the current row and I need to delete the next 6 as well whenever the word "Subtotal" is found.

This is my code:

Sub DeleteRows()
Dim c As Range
Dim SrchRng

Set SrchRng = ActiveSheet.Range("B1", ActiveSheet.Range("B1200").End(xlUp))
Sheets("2014").Select
Do


Set c = SrchRng.Find("Subtotal", LookIn:=xlValues)
If Not c Is Nothing Then c.EntireRow.delete



Loop While Not c Is Nothing


Set SrchRng = ActiveSheet.Range("C1", ActiveSheet.Range("C1200").End(xlUp))
Do
Set c = SrchRng.Find("Subtotal", LookIn:=xlValues)
If Not c Is Nothing Then c.EntireRow.delete
Loop While Not c Is Nothing
End Sub

Could you please help me to resolve this?

RE: Find a word in a cell and delete certain rows bellow

Hi Carmen

Thanks for getting in touch. Should be an easy switch: where your code deletes (c.EntireRow.Delete) replace it with this:

c.Rows("1:6").Delete

Which says "relative to where Subtotal is found, delete the next 6 rows including this one".

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Mon 27 Jan 2014: Automatically marked as resolved.

 

Training courses

 

Training information:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel tip:

Generate randon numbers

Some types of analysis require you to use randomly generated numbers. You can also use randomly generated numbers to quickly populate an Excel spreadsheet. There's an easy function you can use to do this automatically. Here are a few of the ways you can use it:

Type =RAND() in a cell to generate a number between 0 and 1.
Type =RAND()*100 to generate a number between 1 and 100.

View all Excel hints and tips


Server loaded in 0.08 secs.