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]

Can I ask also something more in case it can be included in this thread?

A. The values in Column AK are in brackets i.e. (1:1). Is it possible for the brackets to be removed in the whole column without the format of this column to be changed? I want it to be in a text format.
B. Is it also possible when this is over, in the whole sheet to replace the dot with a comma (. ,) where it exists and remove all the data in cells that contain only the symbol "-"?


[TABLE="width: 456"]
<colgroup><col span="2"></colgroup><tbody>[TR]
[TD]Case A[/TD]
[TD]Case B[/TD]
[/TR]
[TR]
[TD]AK[/TD]
[TD]Any Cell contains this calue[/TD]
[/TR]
[TR]
[TD](1:1)[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Outcome[/TD]
[TD]Outcome[/TD]
[/TR]
[TR]
[TD]1:1[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Of course I want all of these to be included in the same macro if possible. I am trying to receord these movements to include them in your macro but something is not being right.

Thanks!

 

[Pettor]

In case B we have to replace also the dot with a comma.

Thanks!

 

[Peter_SSs]

Untested, but try adding the blue code where shown.

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, "AK").Value) Or Cells(r, "AK").Value Like "[[]*]" Then
      Rows(r).Delete
    ElseIf IsEmpty(Cells(r, "AJ").Value) Then
      Cells(r - 1, "AL").Value = Cells(r, "AK").Value
      Rows(r).Delete
    End If
  Next r
  With Range("AK1", Range("AK" & Rows.Count).End(xlUp))
    .NumberFormat = "@"
    .Replace What:="(", Replacement:="", LookAt:=xlPart
    .Replace What:=")", Replacement:="", LookAt:=xlPart
  End With
  With ActiveSheet.UsedRange
    .Replace What:=".", Replacement:=",", LookAt:=xlPart
    .Replace What:="-", Replacement:="", LookAt:=xlWhole
  End With
  Application.ScreenUpdating = True
End Sub

 

[Pettor]

Great! I will do the testing and revert.

Thanks!

 

[Pettor]

I have the problem with the column AK that doesn't keep the text format. When I or the code removes the brackets because it jas the : in the middle, automatically it converts the format in to date. Is there any way to change that?

 

[Peter_SSs]

I have the problem with the column AK that doesn't keep the text format. When I or the code removes the brackets because it jas the : in the middle, automatically it converts the format in to date. Is there any way to change that?

Yes, sorry, as I said my suggestion was untested & I forgot about Replace changing the format even though I had set it to text with the "@".

After the various rows have been deleted, are all the values in AK (excluding the heading) surrounded by parentheses or only some?

 

[Pettor]

Yes all of them have brackets except the first line with the heading

 

[Peter_SSs]

Yes all of them have brackets except the first line with the heading

Then replace that first block of blue code in post 23 with this

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

 

[Pettor]

Awesome!!!!

Thanks!

 

[Peter_SSs]

Awesome!!!!

Thanks!

Cheers.