The Cells object takes two arguments: row and column in that order:
Cells(row, column)
The column can be defined by number or a letter ie to reference cell B5 you could write Cells(5, 2) or Cells(5, "B")
The macro uses the variable myRow for the row number and different numbers to define the columns for the various cell references.
If you want to change the columns just change the second argument in Cells for the corresponding column number or simply use the letter. Eg. change column B to column C: instead of Cells(myRow, 2) change to Cells(myRow, 3).
Hope this is clear. Let me know if you need further clarifications.
This question has been answered already with the macro below (however, the author of it did not explain how to change the uniqueness by ADDING or DELETING columns). So if someone can abbreviate or explain how to manipulate this macro to better understand it--thanks.
Sub BDEJKW()
Dim myRow As Integer
For myRow = 2 To ActiveSheet. UsedRange. SpecialCells(xlCellTypeLastCell). Row
Cells(myRow, 32) = Cells(myRow, 2) & "," & _
Cells(myRow, 4) & "," & _
Cells(myRow, 5) & "," & _
Cells(myRow, 10) & "," & _
Cells(myRow, 11) & "," & _
Cells(myRow, 23)
Next myRow
End Sub
Can't you just Use a concatenate formula in a cell and then copy and paste it into the rest?
=CONCATENATE(B1,", ",D1,", "E1,", ",J1,", ",K1,", ",W1)
Additional:
Well if you NEED a macro:
Sub JoinCells()
Dim MaxCell As Integer
Dim rowN As Integer
Dim nsheet As Integer
Dim dateVal As String
Dim MyFormula as string
MyFormula = "CONCATENATE(B1,', ',D1,', ',E1,', ',J1,', ',K1,', ',W1)"
nsheet = Application.ActiveWorkbook.
ActiveSheet.Index 'this goes on line above
rowN = 1
MaxCell = Application.ActiveWorkbook.
ActiveSheet.UsedRange.Rows.Count 'this goes on line above
Do Until rowN = MaxCell + 1
If isnull(Worksheets(nsheet).Cells(rowN, 1)) Then 'Check for blank cell in first column
'skip this row as it's blank
Else
Worksheets(nsheet).Cells(rowN, 32).Select 'this should be column AF
ActiveCell.FormulaR1C1 = MyFormula
End If
rowN = rowN + 1
Loop
End Sub
Untested, but it should get you close
I need help with a MS EXCEL MACRO.
I have SIX (6) Columns of data that range anywhere from 5,000 to upwards of 25,000 (it fluctuates; therefore, the macro needs to stop once an empty cell is hit).
The columns are "B, D, E, J, K, W".
Each column needs to be separated by a "COMMA".
Data needs to be placed in Column "AF" as it is computed by the Macro.
NOTE: This is NOT alpha-numeric NOR numerical characters. This is solely WORDS/ALPHABETIC.
EXAMPLE:
ABCA (Column B)
ABCB (Column D)
ABCC (Column E)
ABCD (Column J)
ABCE (Column K)
ABCF (Column W)
Needs to become:
ABCA,ABCB,ABCC,ABCD,ABCE,ABCF
