Find last instance of a word and insert blank row

**HelenW** · 12-11-2008, 12:10 PM

Hi everyone (again!)

I hope I'm not posting too often on here? I have another thing I'd like to do, and would appreciate the help/guidance.

In my spreadsheet of data, I would like to be able to add vb code to my developing macro that searches within 1 column for the last instance of a particular word, then inserts a blank row under that word. All the data is sorted, so the words will be used multiple times, but I need the row to go under the last instance of each word in the column.

Hope you can help!

**royUK** · 12-11-2008, 12:16 PM

Is this in your last workbook, if so which sheet are you referring to?

**Shijesh Kumar** · 12-11-2008, 12:24 PM

See the attachemet if this is of any help to you

**HelenW** · 12-11-2008, 12:31 PM

Sorry Roy - didn't want to assume you'd still be there to help! Yes, it's my last workbook but have attached the one I am working with as it is less clogged up with macros.

I want to seach down column F, find the last instances of Eastern, Southern, and Northern, and insert a blank row underneath the last instance of each to separate each section. I would want to format this row by filling the row up to column N with another colour.

There will be a differing amount of data each time the spreadsheet is used, and it is possible that one of the above instances might not appear - can that be incorporated?

**HelenW** · 12-11-2008, 12:33 PM

Thanks Shijesh - can that be automated so that you don't have to type in the words to find? It will always be the same 3 words.

ETA that I think I figured out to do it! Thanks!

**royUK** · 12-11-2008, 12:41 PM

Which column is it? Do you just want to add blank rows whenever there is a change in the names?

**Shijesh Kumar** · 12-11-2008, 12:41 PM

Hope this will be near to what you want if not the exact

see attachemnt

**Shijesh Kumar** · 12-11-2008, 12:46 PM

Originally Posted by Shijesh Kumar

Hope this will be near to what you want if not the exact

see attachemnt

Ignore the above Post...
I didn't see your above post....

**HelenW** · 12-11-2008, 12:47 PM

OK, I had this working, then it stopped and says that the variable is not defined (c). This is what I had working, and it inserted a row under the last instance of Northern, of Southern and of Eastern (they are sorted already, we just won't know how many there will be each time).

Please Login or Register  to view this content.

**HelenW** · 12-11-2008, 12:48 PM

Yes, a blank row whenever there is a change in name in column F.

**Shijesh Kumar** · 12-11-2008, 12:55 PM

Hi

The above code is perfect...
Its running without any error ....

**Shijesh Kumar** · 12-11-2008, 01:01 PM

code to automate it

In VBA editor double click on Sheet1(Data) and put the code there...
Don't put this code in module .... put it in sheet where u want to run this code

Please Login or Register  to view this content.

**HelenW** · 12-11-2008, 01:01 PM

Just ran it again, and you're right - it works. Onto the next bit now! Thanks for your help (again!).

**HelenW** · 12-11-2008, 01:06 PM

What an idiot - just realised that I need to insert a row ABOVE the FIRST instance, not below the last! How do I change to code to do that please??

**royUK** · 12-11-2008, 01:08 PM

This will add a row at each change in value of Column F

Please Login or Register  to view this content.

**Shijesh Kumar** · 12-11-2008, 01:17 PM

Originally Posted by HelenW

What an idiot - just realised that I need to insert a row ABOVE the FIRST instance, not below the last! How do I change to code to do that please??

modified code

Please Login or Register  to view this content.

**HelenW** · 12-11-2008, 01:18 PM

Thanks Roy - that works, except it doesn't differentiate between Southern and Strategic, and leaves them together as one big block - any ideas?

**HelenW** · 12-11-2008, 01:21 PM

I'm off now, but will definitely be returning to this next week! Thanks for your help so far.

**royUK** · 12-11-2008, 01:24 PM

It works fine for me.

**darrell@ami** · 12-10-2010, 12:10 AM

Please Login or Register  to view this content.

I used this instead of column F I changed it to a range.
But it just cycles through and doesn't insert and doesnt give error.
Can someone help me out?