Macros : How to add multiple cells into one depending on another cell & delete empty cells after

[brodyb1]

Hello,

I'm trying to figure out in Macros how to add the multiple cells under description (M:M) into one cell, so its one continuous descriptions . The one cell with the full description needs to be on the same row with "item" under (A:A). So basically row 3 would have the the information on row 3, whereas now under the "Description" column, the description is broken up into two cells. Then after all the information has been consolidated into one row I want to delete the empty rows between each "Item" (A:A). In the end each row with "item" has all the information, and there are no empty spaces between the rows.

Sorry if I'm running in circles I've been trying to describe my problem/goal accurately. I'm also new to Macros but I've done some successful codes in the past few month but this seems far out of my reach and I was hoping someone here could help me.

I ideally want it to look like this (below). Which is did with filters and filtered out the blanks, but with filters it cuts my descriptions short, which is the problem.

https://imgur.com/acpRlfY

 

[Fluff]

Hi & welcome to the board.
How about

Sub ConsolidateDelete2()

   Dim Rng As Range
   
   With Columns(1).SpecialCells(xlBlanks)
      For Each Rng In .Areas
         Rng.Offset(-1, 12).Resize(1, 1).Value = Join(Application.Transpose(Rng.Offset(-1, 12).Resize(Rng.Count + 1).Value), ", ")
      Next Rng
      .EntireRow.Delete
   End With
   
End Sub

 

[brodyb1]

Hello,

When I run it I get

 

[Fluff]

In that case your "Empty" cells in col A aren't empty.
How is the data being entered?

 

[brodyb1]

The data is from a program called CostPoint and I just use the export as excel function. The cells seem empty when I click on the empty cells in col A. Is their a way I can send you the file?

 

[brodyb1]

 

[Fluff]

You can upload the file to OneDrive, dropbox, mark it for sharing & then post the link to this thread.

 

[Fluff]

This might work, depending on your data

Sub ConsolidateDelete2()

   Dim Rng As Range
   
   With Range("A2", Range("B" & Rows.Count).Offset(, -1))
      .Replace "Item", "=Item", , , False, , False, False
      For Each Rng In .SpecialCells(xlConstants).Areas
         Rng.Offset(-1, 12).Resize(1, 1).Value = Join(Application.Transpose(Rng.Offset(-1, 12).Resize(Rng.Count + 1).Value), ", ")
      Next Rng
      .SpecialCells(xlConstants).EntireRow.Delete
      .Replace "=Item", "Item", , , False, , False, False
   End With
   
End Sub

 

[brodyb1]

https://www.dropbox.com/s/2mu9jma7wot30fv/Excel Trial Sheet.xlsx?dl=0

 

[brodyb1]

THAT WORKED! Would you you explain what each command is doing? I'd like to learn as well

Thank you!