Define a range while excluding blanks

Hey everyone! I'm working on a macro that can be used to help process chat transcripts into a more appropriate format. Right now the chats are staggered with names in Column A and then the message that was sent 1 row down and over in Column B.

The end result involves using a paste, skip blanks macro to shift the cells in Column B over to the left and then I have a 2nd macro which will insert a blank row above each name from Column A. I'm attempting to define the names in Column A as Chat_Names prior to adding in the data from Column B but I can't seen to define just the names while skipping all the blank cells.

If I define the entire range, it ends up inserting a blank row above each name and each chat statement when the stuff in column B gets pasted over.

I'm tried using filter & excluding blanks with selecting visible cells only and GoTo constants. Both those methods managed to select just the names and exclude the blanks in Column A, but I wasn't able to define a name with those selection methods. The actual document is around 2000 rows long so selecting each individual cell is also not a viable option.

I've attached a short excel file showing a basic example of what I'm trying to do with the 2 current macros. Hopefully someone can help me figure this out. Thanks!

Thanks Marcol. That workaround managed to format it properly but part of the reason I was looking to insert rows above each name is because I have additional data that is relevant to the responses in 3 more columns going to the right. Each response during a chat can have up to 3 tags assigned to it so I'm not sure how to incorporate the same function to include the additional data necessary. I've included a revise test document with two tabs for pre and post formatting.

One other thing I'm concerned with is prior to all the cell shifts into the final format for the deliverable, there's quite a bit of text formatting that occurs as well. All the tags (columns CDE on the 1st sheet, EFG on the 2nd sheet) are filled with a particular color and the name of the chat moderator, John in this case, and the question they asked also have a fill color added to the cells.

I know previously when I was trying to work through this problem I couldn't manage to get a index function to work properly while maintaining the formatting from individual cells. The last time I built the deliverable transcript I just added a new column A and used:
=IF(ISNUMBER(SEARCH("STRING HERE",”CELLS”)),"1","")
to search for all the cells that would have contained John in column A and then used a separate macro to insert a Line above each instance of "John". Following that I ended up shifting the column A contents down 2 rows and insert a row above each respondent message (Bill) in this case.

It was quite a long workaround before but the major problem with that solution was if John asked 2 consecutive questions, it would end up messing up the formatting upon shifting the cells down since that solution would require constantly alternating messages between John/Bill to work properly.

Tried to modify the function you gave me but it didn't turn out too well...

Please Login or Register  to view this content.

If you can't tell I haven't worked much with the countif/indexing, haha. I'm not 100% but I'm thinking I might be able to write a quick VB macro to select each individual cell in the range and apply a defined name to them so that later in the process after all the formatting is done I could still reference them to add the spaces then. If that manages to work I'll post an update soon enough.

Best that you post a sample workbook, showing before and after.
Any anomolies should also be included, ideal scenarios hardly ever exist.

Blast, thought I had attached it previously. Sorry

I actually revised my previous method and got it to work correctly. Prior to pulling all the data for formatting the names are separated so I just added two columns and then applied a filter. Added a 1 next to all the chat moderator names and then filtered them out and added a 2 next to all the respondent names. After that I was able to just modify the macro below to insert rows base on the 1 or 2 so it all ended up corresponding perfectly in the end. Thanks for the help though.

Please Login or Register  to view this content.