How to remove multiple empty rows between grouped sets of data (arrays ranges) but leave only one row line empty between each one

asked 2016-04-14 08:32:11 +0100

p gravatar image

How to remove multiple empty rows between grouped sets of data (arrays ranges) but leave only one row line empty between each one?

Multiple ranges are contiguous in rows of data and would like to remove all the extra lines (anything more than one and only preserve one).

How to accomplish this task with:

Question 1) multiple ranges in only one column?

Q2) multiple ranges in more than one column?

How would you keep two (or specify more) blank rows between each set of data?

KEYWORDS TAGS calc rows empty blank spaces remove delete edit

edit retag flag offensive close merge delete

Comments

This seems to be another bad idea. As far as I can see it is one of the best proven rules of keeping and maintaining data that they be entered into contiguous rows. Empty rows, if urgently wanted, should be implemented by a dedicated field (column) telling whether any row is "VOID" or "VALID" . Otherwise even the determnation of the variable data range (How many data? How many rows?) is getting more complicated (and less efficient).
See also my comments in the other thread.

Lupp gravatar imageLupp ( 2016-04-14 12:52:36 +0100 )edit

Why not keeping each set of data in a separate Sheet ?

There are no problems to make calculations with data from different sheets .

I totally agree with @Lupp that holding a few sets of data in the same sheet when only some empty lines separate them is a bad idea .

Basically , the solution to your question is to write a macro that runs along a column and based on the data in this column , deletes (or not) the rows .

no more space .

moshe11 gravatar imagemoshe11 ( 2016-04-15 01:22:55 +0100 )edit

I'll continue here .

The logic behind the macro will be something like :

If this cell is empty and also the cell above is empty , then , delete this row .

And in case you want 2 empty rows ,

check that this cell is empty and the 2 cells above are empty ,

and only then you delete the row .

BUT , again , you are better off splitting the sets of data to different sheets .

moshe11 gravatar imagemoshe11 ( 2016-04-15 01:31:51 +0100 )edit