So, I got some code from the great people at this forum, and was wondering if it could be tweaked a bit.
The coded copies txt from one field to another, and puts itself at the top of the cell and removes the txt from the previous cell.
The problem is, if cells in the column are blank it has started to put a square symbol in, so when we autofilter it will show squares. I can work around it by filtering for the squares and deleting them manual which is still a time saver. The problem is if the squares get put in between other txt there's no way to delete them. I can live with it, but figured it would be worth asking, as it did not always do this.
Thanks in advance
Sub AJ2AI()
Dim cell As Range
Dim s As String
Dim r As Range
Dim sFrom As String
Dim sTo As String
Dim lFrom As Long
Dim lOffset As Long
Dim rRange As Range
Dim rTop As Range
Dim rBottom As Range
Dim sh As Worksheet
sFrom = "AJ"
sTo = "AI"
On Error GoTo EF
Application.EnableEvents = False
lFrom = Range(sFrom & 1).Column
lOffset = Range(sTo & 1).Column - lFrom
Set sh = ActiveSheet
With sh
Set rTop = .Cells(2, lFrom)
Set rBottom = rTop.Offset(.UsedRange.Rows.Count - 1)
Set rRange = Range(rTop, rBottom)
End With
For Each cell In rRange
With cell
Set r = .Offset(0, lOffset)
s = r.Value
If InStr(s, .Value) = 0 Then
s = .Value & vbLf & s
With r
.Value = s
.VerticalAlignment = xlTop
End With
.Value = "" 'remove this line if you want column(sFrom) to remain as is.
End If
End With
Next cell
EF:
Application.EnableEvents = True
End Sub
Bookmarks