Remove blank rows within cells using Excel VBA

0
votes

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?

3
excelvba

3 Answers

1
votes

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

0
votes

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
0
votes

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.