How to remove blank rows, then insert blank row conditionally, PLUS error proof

[GTS115]

Hello,

This is my first post here. This is a cross post. I read that cross posts are frowned on, but I didn't receive any replies to the second part of my query and what I am trying to do is beyond my (current) abilities. So I'm hoping that this is ok to post. See the following link.

http://www.mrexcel.com/forum/showthread.php?t=413309

Here is the first part of my question. I did get a response and solution to this.

Part 1.
Here's the scenario. Worksheet with data in range A3:K58. Note that the range will always start on row 3, but it is possible that the range might increase from time to time with additional rows. I want to do 2 things.

First, I want to go thru the range and remove any blank rows.

Second, I want to go thru the range from top to bottom and insert a single blank row conditionally. The condition is as follows: Column K is a "check sum" sort of column. It will have a non-zero value at the top, and at some point (row) the value in this column will change to zero and be zero after that for the rest of the range. (An enhancement would be to verify that this is indeed the case.) All I need to do is to insert a blank row above the first row that has a zero in column K.

Here is the second part of my question. I did not get a response and solution to this.

Part 2:
Now if someone could further help me to learn and better error-proof things...

1) It's unlikely but possible that column K could change. eg If someone inserts a column ahead of it, turning it into L. How can I deal with this? Some sort of relative reference, or naming column K as a range?

2) Error check that column K does indeed have the structure expected. (See my first post.) That is, starting at row 3, it should have a non-zero value to 'some' row, then it should change to zero and remain zero for all subsequent rows. I would want to perform this check first. If all is ok, continue. If not, stop and provide message.

Thanks in advance.

[DonkeyOte]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

[GTS115]

Ok, thanks.

I will try to create a dummy workbook in the next day or 2.

[JBeaucaire]

You indicated your data starts at row 3. That usually means titles of some sort above that. If you tell us the exact unique text string that is in Row 1 or Row 2 of column K, then the macro can be made to dynamically spot that unique text across that row and then run the "insert row" evaluation on the column spotted.

This way it will always find the correct column.

Example:

Please Login or Register  to view this content.