Conditional concatenation based on a criteria

sbuba

New Member
Joined
Jul 26, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
I have a file that I frequently analyse. Each time I have to concatenate value in two cells but it is time consuming. I am new to vba and would appreiate if someone cal help me with a script that the will automate the process.
This is what I do:
1. Delete row 1 to row 10
2. Type heading "Classifier" in cell C11
3. Concatenate cell A14 & B14 to C14
4. Concatenate cell A20 & B20 to C20
5. Concatenate cell A25 & B25 TO C25
6. Kill the formulas in ROW C:C
7. Copy the C11 until the end of the lines below, I repeat the same for C20 and C25.

This is time consuming. sometimes I have to do the same process for more than 6000 rows.
 

Attachments

  • Annotation 2021-07-26 145420.jpg
    Annotation 2021-07-26 145420.jpg
    118.8 KB · Views: 5

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
For part 7 do you mean that the concatenated value in C14, C20, & C25 should be repeated until the blank row?
 
Upvote 0
In that case how about
VBA Code:
Sub sbuba()
   Dim Rng As Range
   
   Rows("1:10").Delete
   Range("C1").Value = "Classifier"
   For Each Rng In Range("B2", Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlConstants).Areas
      Rng.Offset(, 1).Value = Rng.Offset(, -1)(1).Value & " " & Rng(1).Value
   Next Rng
End Sub
 
Upvote 0
Thank you will test teat tomorrow and provide feedback. Much appreciated.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,034
Members
448,543
Latest member
MartinLarkin

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
Back
Top