N
N1KO
Hi,
I have this code which Rick helpfully supplied
Sub List()
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
With Worksheets("EmailList")
FirstRow = 2
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For iRow = LastRow To FirstRow + 1 Step -1
If .Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value Then
With .Cells(iRow - 1, "B")
.NumberFormat = "@"
.Value = .Text & "," & .Offset(1, 0).Text
End With
.Rows(iRow).Delete
End If
Next iRow
End With
End Sub
This is excellent however i need now to be able to make the cell with the
list in (Column B) to not go over 250 Characters. If it does i need it to put
the remaining info in the cell below with the same code as in Column A.
Example.
A1 = 111
B1 = e-mail addresses
IF B1's character length goes over 250 i need the rest on the info to go
into B2 and the same code from A1 to be entered into A2.
Column A B
Row
1 111 character length
greater than 250
2 111 rest of characters that
would have been in B1
3 112 character length ok
4 113 character length ok
Following this I need a value entered at the start of every cell in column B.
It will be e-mail addresses in column B all seperated by a comma with no
spaces but i need (e-mail address removed) to appear at the start of every cell in column
B that contains e-mail addresses.
Any help appreciated!
Thanks
I have this code which Rick helpfully supplied
Sub List()
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
With Worksheets("EmailList")
FirstRow = 2
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For iRow = LastRow To FirstRow + 1 Step -1
If .Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value Then
With .Cells(iRow - 1, "B")
.NumberFormat = "@"
.Value = .Text & "," & .Offset(1, 0).Text
End With
.Rows(iRow).Delete
End If
Next iRow
End With
End Sub
This is excellent however i need now to be able to make the cell with the
list in (Column B) to not go over 250 Characters. If it does i need it to put
the remaining info in the cell below with the same code as in Column A.
Example.
A1 = 111
B1 = e-mail addresses
IF B1's character length goes over 250 i need the rest on the info to go
into B2 and the same code from A1 to be entered into A2.
Column A B
Row
1 111 character length
greater than 250
2 111 rest of characters that
would have been in B1
3 112 character length ok
4 113 character length ok
Following this I need a value entered at the start of every cell in column B.
It will be e-mail addresses in column B all seperated by a comma with no
spaces but i need (e-mail address removed) to appear at the start of every cell in column
B that contains e-mail addresses.
Any help appreciated!
Thanks