I have a very large excel spreadsheet 2000+ rows deep.

I am organising it by adding a blank row each time the first word in Column C changes; I have been at it for over half an hour now and this got me wondering.

Can I do this in a automatic matter either via a built in menu or a macro? What should I do?

Please be n00b friendly.

  • Pls illustrate with an example as how the word in column C would change. A macro can certainly do the job by comparing the string in the previous row of column C with the one it is currently on and insert a new row – Prasanna Jul 20 '14 at 13:16
  • I would rethink your approach. I suspect if you convert your range to a Table (from the Insert ribbon) you will find the automatic Filtering Search functionality will meet your requirements, without needing any blank rows or macros. – Mike Honey Jul 21 '14 at 0:10

Recording a Macro in Excel is easy, such a macro is often quite easy to modify to your needs - assuming you have some "BASIC" knowledge (note; Basic knowledge of "BASIC" language).

As you didn't tell which version of Excel you have;

  1. Find "Record new Macro"
  2. close to that you should have "Use relative references" - make that option be "active"
  3. Place the cursor on the top of an item in "Column C", where the first word differs from the NEXT row.
  4. Click on/Select "Record new macro"
  5. Press F2, then END, Type ONE SPACE, press ENTER (This "Edits" the cell contents and makes it appear in the Macro)
  6. Find and select "Insert, Cells, Entire row" in the menus (Or use the keyboard shortcut if you know it).
  7. Hit cursor down ONCE.
  8. Select "Stop recording"

Press ALT+F8 - find the macro, click on the name,
then find the [Options]-button, click on it and set a keyboard shortcut for the macro. Hmm... I believe there is an "Edit" button, not sure though - Close the dialog.

Now, what you need to do is edit this recorded macro... ;-) with your BASIC-knowledge. Press ALT+F11 to get to the macro editor.

If you do not know how to edit it, copy out the macro's code from the editor and paste it in your question above and someone (me maybe) may edit it for you.

| improve this answer | |
  • I like your approach of teaching how to do the job, rather than just doing it for MANICX...... Appreciate it. Kudos @Hannu – Prasanna Jul 20 '14 at 14:53
  • @Prasanna - the task isn't hard, if you just get past the initial threshold :-) – Hannu Jul 20 '14 at 15:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.