I need help writing a code for excel that takes all active data in a row and concatenates it into one cell and then have this loop through all active rows.
The problem I am running into is that the amount of columns and rows is completely random so I just need it to take into account all existing data.
Lastly, I want the concatenation to only include letters, so no numbers or "," or anything else.
Please help if you are able.
Here is a before and after images, if that helps.
Before
After
That might work - that macro avaoids every special character and numbers. Copy and Paste that code into a new module in your VBA editor and run the sub "CreateString".
Sub CreateString()
Dim LastRow%: LastRow = ActiveSheet.UsedRange.Rows.Count
Dim strConcatenate$, i%, j%
Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet: Set ws = wb.ActiveSheet
ws.Range("A:A").Clear
For i = 1 To LastRow
For j = 2 To LastColumn(i) 'Calls function "LastColumn" to get the last column of each row
strConcatenate = strConcatenate & FindReplace(ws.Cells(i, j))
Next j
ws.Cells(i, 1) = strConcatenate 'This will past the finished string into column [A] in the specific row
strConcatenate = "" 'blanks the string, so the next string in the next row is fresh
Next i
End Sub
Function LastColumn(row%) As Integer
Dim ws As Worksheet: Set ws = ThisWorkbook.ActiveSheet
LastColumn = ws.Cells(row, ws.Columns.Count).End(xlToLeft).Column
End Function
Function FindReplace(CellValue$)
Dim strPattern$: strPattern = "[^A-Za-z]+" 'Pattern to only take care of letters
Dim strReplace$: strReplace = "" 'Replace everything else with blank
Dim regex As Object
Set regex = CreateObject("vbscript.regexp")
With regex
.Global = True
.MultiLine = True
.IgnoreCase = False
.Pattern = strPattern
End With
FindReplace = regex.Replace(CellValue, strReplace) 'RegEx Function replaces the pattern with blank
End Function
Example:
