Concatenate

Sufiyan97

Active Member
Joined
Apr 12, 2019
Messages
394
Office Version
  1. 2013
Platform
  1. Windows
Hello everyone,

I am using below formula and I don't have any problem with it but I need to do some manual formatting like deleting blank rows and deleting column B and D

Cell Formulas
RangeFormula
C1:C15C1=IF(A1,D1,"")
D1:D15D1=IF(A2,B1,B1&" "&D2)


I am looking for a Macro to do it automatically to get result like below

Book8
AB
11A B C D E
22A B C D
33A B C
44A B
52A
Sheet1
 

Sufiyan97

Active Member
Joined
Apr 12, 2019
Messages
394
Office Version
  1. 2013
Platform
  1. Windows
How about
VBA Code:
Sub alidurfani()
   Dim Rng As Range
  
   For Each Rng In Range("A1:A" & Range("B" & Rows.Count).End(xlUp).Row).SpecialCells(xlBlanks).Areas
      Rng.Offset(-1, 1).Resize(1).Value = Join(Application.Transpose(Rng.Offset(-1, 1).Resize(Rng.Count + 1).Value), " ")
   Next Rng
   Range("A:A").SpecialCells(xlBlanks).EntireRow.Delete
End Sub
Thank you Fluff,

Works Perfect!
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,252
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Sufiyan97

Active Member
Joined
Apr 12, 2019
Messages
394
Office Version
  1. 2013
Platform
  1. Windows
Hello Fluff,

This code is very useful in our office and saving our time and I am getting credit because of this and when my boss seen this code he said me you are a champ!

but all actual credit belongs to you only.

Thank you very much again 💐!
 

Forum statistics

Threads
1,141,063
Messages
5,704,064
Members
421,326
Latest member
pfaustino

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top