Excel Macro 1

[chester27]

Looking for some help or advice. Trying to create a Macro that will concatenate 4 columns A-D with a ~ separator in Column F. It needs to start in E2 and continue till empty cell in column D. I have tried the below so far but fails to continue past E3.

Sub ConCat()
'
' ConCat Macro
'

'
Dim oWS As Worksheet, lLastRow As Long, r As Long

Set oWS = ActiveSheet
lLastRow = oWS.Cells.SpecialCells(xlLastCell).Row
For r = 1 To lLastRow
' Combine A to D
If Len(oWS.Cells(r, 4)) > 0 And Len(oWS.Cells(r, 5)) > 0 Then
oWS.Cells(r, 5).Value = oWS.Cells(r, 1).Value & "~" & oWS.Cells(r, 2).Value & "~" & oWS.Cells(r, 3).Value & "~" & oWS.Cells(r, 4).Value
End If
Next
End Sub

 

[zelgar]

I think your problem is with this line: lLastRow = oWS.Cells.SpecialCells(xlLastCell).Row
Try changing it to the following : lLastRow = oWS.ActiveCell.SpecialCells(xlLastCell).Row

I also don't understand why you say you want to start in E2, but end when the data is empty in col D. Is there a reason not to use a Do Loop

Sub ConCat()
 '
 ' ConCat Macro
 '

 '
 Dim oWS As Worksheet, lLastRow As Long, r As Long

 Set oWS = ActiveSheet
[s] lLastRow = oWS.Cells.SpecialCells(xlLastCell).Row
 For r = 1 To lLastRow
[/s][COLOR=#EF2929]r = 1    ' Or change this to the first row of data
Do Until IsEmpty(Cells(oWS.Cells(r, 4))[/color] ' Combine A to D
[indent]If Len(oWS.Cells(r, 4)) > 0 And Len(oWS.Cells(r, 5)) > 0 Then
oWS.Cells(r, 5).Value = oWS.Cells(r, 1).Value & "~" & oWS.Cells(r, 2).Value & "~" & oWS.Cells(r, 3).Value & "~" & oWS.Cells(r, 4).Value
[s]End If[/s]
[COLOR=#EF2929]r = r + 1
[/indent]Loop[/color]
 Next
 End Sub

Additionally, unless you are using the code for multiple spreadsheets, you can drop the oWS. throughout the macro

 

[SkipVought]

Hi,

So your If statement starts off... If the length of the value in D1 And the lengthy of the values in E1 are greater than ZERO then do the concatenation. Same for the next and so on for each row.

We can't see your data, so ????

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[chester27]

Skip
Yep my bad, "If Len(oWS.Cells(r, 4)) > 0 And Len(oWS.Cells(r, 5)) > 0 Then"..... should have read "If Len(oWS.Cells(r, 1)) > 0 And Len(oWS.Cells(r, 4)) > 0 Then...."

All works a treat now

Zelgar
Do Until IsEmpty(Cells(oWS.Cells(r, 4)) ' Combine A to D keeps throwing a debug error but not too fussed as it now works as I want it, and going forward we will be using multiple spreadsheets, but thanks

 

[Andrzejek]

It would be nice if you would post your solution to your problem so others - who may have the same/similar issue - can benefit from this thread.

"now works" doesn't really help anybody since nobody knows what you have done...

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[chester27]

Andy

I thought I had, I only changed one line as it was more an oversight on my part, but just in case it is misread here it is.

Sub ConCat()
'
' ConCat Macro
'

'
Dim oWS As Worksheet, lLastRow As Long, r As Long

Set oWS = ActiveSheet
lLastRow = oWS.Cells.SpecialCells(xlLastCell).Row
For r = 1 To lLastRow
' Combine A to D
If Len(oWS.Cells(r, 1)) > 0 And Len(oWS.Cells(r, 4)) > 0 Then
oWS.Cells(r, 5).Value = oWS.Cells(r, 1).Value & "~" & oWS.Cells(r, 2).Value & "~" & oWS.Cells(r, 3).Value & "~" & oWS.Cells(r, 4).Value
End If
Next
End Sub

Hope this helps.

Cheers

Craig