?
Solved

Sub to delete rows  after key word

Posted on 2011-05-13
3
Medium Priority
?
171 Views
Last Modified: 2012-05-11
I have a spreadsheet, which has a key word called "SYSTEMICS" (Highlighted Yellow)

I want to be able to delete all rows after that word is found.

I have attached an example, it is always in ColB

Thanks
Seamus
test.xls
0
Comment
Question by:Seamus2626
3 Comments
 
LVL 34

Expert Comment

by:Rob Henson
ID: 35754267
Does it have to be VBA?

A helper column with a count of occurences of the word in cells above will show which to delete.

Assuming column C is available enter in C2

=COUNTIF(B$2:B2,"SYSTEMICS")

Copy down for extent of data.

AutoFilter on this column for greater than zero and delete the rows showing in the filter.

Cheers
0
 
LVL 6

Accepted Solution

by:
Eric Zwiekhorst earned 2000 total points
ID: 35754439
please look at file with macro

Kr Eric
test-macro-.xls
0
 

Author Closing Comment

by:Seamus2626
ID: 35754581
Hi robhenson, i needed vba code for this as i need it to run as a procedure

Thanks Zwiekhorst

Cheers,
Seamus
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question