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

find word cell and

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

ResolvedVersion 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:

How to apply the same formatting and data to multiple sheets at the same time in Excel 2010

When you want to format more than one sheet in a worksheet exactly the same way, Ctrl-click the tabs of the sheets you want to group together and they will all turn white. While they are grouped, anything you enter in one sheet gets entered into the others.

After you have done this, remember to click on the tabs to take them out of the group so that you do not accidentally insert data in multiple sheets when you just want to insert data in one.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.1 secs.