CODEWITHSUNDEEP
excelvba
James-Jesse Drinkard
James-Jesse Drinkard

Reputation: 15723

Remove blank rows within cells using Excel VBA

I'm tasked with removing blank rows within the cells themselves, not blank rows in the spreadsheet. The data in each cell is in this structure:

==== Lorem ipsum dolor sit amet, consectetuer adipiscing elit ====

Problem: Lorem ipsum dolor sit amet, consectetuer adipiscing elit

==== Lorem ipsum dolor sit amet, consectetuer adipiscing elit ====

Problem: Lorem ipsum dolor sit amet, consectetuer adipiscing elit

==== Lorem ipsum dolor sit amet, consectetuer adipiscing elit ====

Problem: Lorem ipsum dolor sit amet, consectetuer adipiscing elit

==== Lorem ipsum dolor sit amet, consectetuer adipiscing elit ====

Lorem ipsum dolor sit amet, consectetuer adipiscing elit Lorem ipsum dolor sit amet, consectetuer adipiscing elit

==== Lorem ipsum dolor sit amet, consectetuer adipiscing elit ====

This is just a small sample. Each individual cell has approximately 70 lines just like it of varying text. There is no consistent size or exact pattern.

We are using Excel 2010. The actual task is to remove the blank line after each line with the "====" in it.

My question is, can this be done in Excel VBA and if so, how do I go about coding this?

Upvotes: 0

Views: 2633

Answers (3)

Gaijinhunter
Gaijinhunter

Reputation: 14685

Just do a simple search and replace. Hold ALT and press 0010 to insert the line break character in the search and replace box. It won't show anything but it'll be there.

Upvotes: 0

Jerry Beaucaire
Jerry Beaucaire

Reputation: 3197

I'm not entirely sure you even need to loop. Doesn't this do the same thing all at once:

Range("A1:A100").Replace "====" & Chr(10), "==== ", xlPart

...or even:

Range("A1:A100").Replace Chr(10), " ", xlPart

Upvotes: 0

Tim Williams
Tim Williams

Reputation: 166755

Something like this should work

dim c as range
for each c in activesheet.range("A1:A100").cells
   c.value = replace(c.value, "====" & chr(10), "==== ") 
next c

...adjust range to suit

Upvotes: 1

Related Questions