[Macro] Remove blank rows (apart from 1 to separate datasets)

**Hyflex** · 06-08-2011, 04:10 PM

Hi Guys, I'm looking for some help creating a macro to help fix my master document.

The problem is that my master document sometimes gets loads of blank rows with no data in them, basically I need a macro to remove all but one blank row between each dataset

In the example given I have marked the rows to be removed in red and the green ones are to keep, however the top 5 rows of the document should not be touched.

See attached document for a better visual explanation.

Help is much appreciated
Regards
- Hyflex

**mojo249** · 06-08-2011, 04:36 PM

Hi

See your amended workbook attached.

The macro looks for empty cells in column A and deletes the entire row if there is more than 1 consecutive empty row.

I'm sure someone will now point out that Excel has a built in function which will do a similar thing.

Dion

Please Login or Register  to view this content.

**Mordred** · 06-08-2011, 04:39 PM

Hi Hyflex try:

Please Login or Register  to view this content.

This searches through column A to find consecutive empty rows. I would test it on a copy of your original workbook though, just to be safe.

Regards:

**Mordred** · 06-08-2011, 04:39 PM

Sorry mojo, I didn't see that you posted before me.

**watersev** · 06-08-2011, 04:47 PM

hi, Hyflex, please check attachment, run code "test"

**Hyflex** · 06-08-2011, 05:37 PM

Thank you very much guys, 3 choices but I have no idea what ones best/fastest :S

Watersev's is very complicated to my eyes but looks good, you guys are all very helpful

thank you

**Mordred** · 06-08-2011, 05:41 PM

You're welcome Hyflex. If you have the answer(s) you want then please mark the thread as solved and don't be shy, hit up the scales for those that helped.

Cheers!