VBA_Padawan_37
New Member
- Joined
- Apr 23, 2017
- Messages
- 5
Hello,
First time poster long time lurker here! I have recently started getting into VBA and am struggling with a spreadsheet I am working on currently. I have a matrix filled out with either an X or an A in each cell (my rows are IDs and columns are Dates). What I want to do is, for each row, concatenate the values in all of the populated columns (x or a) and return the value in the first column without any data.
I can get the concatenate piece to work just fine, but when I try to wrap it in a For/Next loop it's still only doing the first row. Can you please take a look at the below code and let me know what I'm doing wrong? Number of Rows and Columns can vary from sheet to sheet.
The ultimate goal is to identify which rows have identical values for all of the columns and then find a way to signify rows that are the same (in the example, have all of the same values in columns B through AB). I would like to be able to highlight them but having another column which groups them by assigning a number to the various groups works as well.
Current Code:
Sub Xmatrix_Concatenate()
'
' Xmatrix_Concatenate Macro
'
'
'
Dim s As String
Dim WS As Worksheet
Dim LastColumn As Long
Dim LastRow As Long
Set WS = ActiveWorkbook.ActiveSheet
LastColumn = WS.Cells(1, WS.Columns.Count).End(xlToLeft).Column
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For r = 2 To LastRow Step 1
Do Until ActiveCell.Column = LastColumn + 1
If ActiveCell.Offset(0, 1).Value <> "" Then s = s & ActiveCell.Value & "," Else s = s & ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Loop
ActiveCell.Value = s
Next r
End Sub
Thank you all for your help!
First time poster long time lurker here! I have recently started getting into VBA and am struggling with a spreadsheet I am working on currently. I have a matrix filled out with either an X or an A in each cell (my rows are IDs and columns are Dates). What I want to do is, for each row, concatenate the values in all of the populated columns (x or a) and return the value in the first column without any data.
I can get the concatenate piece to work just fine, but when I try to wrap it in a For/Next loop it's still only doing the first row. Can you please take a look at the below code and let me know what I'm doing wrong? Number of Rows and Columns can vary from sheet to sheet.
The ultimate goal is to identify which rows have identical values for all of the columns and then find a way to signify rows that are the same (in the example, have all of the same values in columns B through AB). I would like to be able to highlight them but having another column which groups them by assigning a number to the various groups works as well.
Current Code:
Sub Xmatrix_Concatenate()
'
' Xmatrix_Concatenate Macro
'
'
'
Dim s As String
Dim WS As Worksheet
Dim LastColumn As Long
Dim LastRow As Long
Set WS = ActiveWorkbook.ActiveSheet
LastColumn = WS.Cells(1, WS.Columns.Count).End(xlToLeft).Column
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For r = 2 To LastRow Step 1
Do Until ActiveCell.Column = LastColumn + 1
If ActiveCell.Offset(0, 1).Value <> "" Then s = s & ActiveCell.Value & "," Else s = s & ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Loop
ActiveCell.Value = s
Next r
End Sub
Thank you all for your help!
