Deleting Lines with a Macro Command

[Pettor]

Hello guys,

I have an excel file with some records that are copied/pasted from a website.
Due to the site's structure when I paste the data on excel, some cells cause the line to split (see an example below) and the surname of the person goes to the below line which except this cell all the others are empty.
What I want to do is to create a macro that will get the Surname (in this case "Wick"), will paste it to the last cell of the upper row (besides the name) and will delete the unecessary line afterwards.
This is not happening in all lines so I need also a way excel to figure out which lines are empty (probably based on the empty values of the other cells in this line) and delete them when the paste is done.

Any advice on that? Many thanks!

[TABLE="width: 195"]
<tbody>[TR]
[TD]Nickname[/TD]
[TD]Age[/TD]
[TD]Name
[/TD]
[/TR]
[TR]
[TD]Thunder[/TD]
[TD]30[/TD]
[TD]John
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Wick
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Pettor]

The code we have used is below:


Sub MoveNameDeleteRow_v5()
Dim r As Long

Application.ScreenUpdating = False
For r = Cells.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row To 2 Step -1
If IsEmpty(Cells(r, "AL").Value) Or Cells(r, "AL").Value Like "[[]*]" Then
Rows(r).Delete

ElseIf IsEmpty(Cells(r, "AK").Value) Then
Cells(r - 1, "AM").Value = Cells(r, "AL").Value
Rows(r).Delete
End If

Next r
With Range("AL2", Range("AL" & Rows.Count).End(xlUp))
.Replace What:=")", Replacement:="", LookAt:=xlPart
.TextToColumns DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(1, 2))
End With

With ActiveSheet.UsedRange
.Replace What:=".", Replacement:=",", LookAt:=xlPart
.Replace What:="-", Replacement:="", LookAt:=xlWhole
End With Application.ScreenUpdating = True

End Sub