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]

Hi Peter,

A. Your code in regards to the text in brackets, indeed erases the FOURTH line, which is the desired outcome and eliminates completely its data. I don't know if they are transfered in the AL2 cell and then are replaced by the data of the line 3. Below I am giving you an example. We can either have a record splitedin two rows (Case A) or splited in thre rows (Case B). The first row includes the headers so it will remain as it is. No need to do anything with it.

So in both cases A & B I need the code to return the data like the table with the desired outcome. Lines 3 or and 4 where it exists to be deleted comlpetely. In the case A the data of the cell AK3 have to be moved to the cell AL2 to have all the data in one line and in the case of a forth line with text in brackets the complete line has to be deleted including also the data in the AK4 cell.


Case A

<colgroup><col style="mso-width-source:userset;mso-width-alt:4059; width:83pt" width="111" span="4"> </colgroup><tbody>
[TD="class: xl65, width: 111"][/TD]
[TD="class: xl65, width: 111"]AI[/TD]
[TD="class: xl65, width: 111"]AJ[/TD]
[TD="class: xl65, width: 111"]AK[/TD]

[TD="class: xl65, width: 111"]1[/TD]
[TD="class: xl65, width: 111"]Nickname[/TD]
[TD="class: xl65, width: 111"]Age[/TD]
[TD="class: xl65, width: 111"]Name[/TD]

[TD="class: xl65, width: 111"]2
[/TD]
[TD="class: xl65, width: 111"]Thunder[/TD]
[TD="class: xl65, width: 111"]30[/TD]
[TD="class: xl66"]John[/TD]

[TD="class: xl66"]3[/TD]
[TD="class: xl66"][/TD]

[TD="class: xl65, width: 111"]Wick[/TD]

</tbody>

<colgroup><col style="mso-width-source:userset;mso-width-alt:4059; width:83pt" width="111" span="4"> </colgroup><tbody>
[TD="width: 111"]Case B[/TD]
[TD="width: 111"][/TD]
[TD="width: 111"][/TD]
[TD="width: 111"][/TD]

[TD="class: xl65, width: 111"][/TD]
[TD="class: xl65, width: 111"]AI[/TD]
[TD="class: xl65, width: 111"]AJ[/TD]
[TD="class: xl65, width: 111"]AK[/TD]

[TD="class: xl65, width: 111"]1[/TD]
[TD="class: xl65, width: 111"]Nickname[/TD]
[TD="class: xl65, width: 111"]Age[/TD]
[TD="class: xl65, width: 111"]Name[/TD]

[TD="class: xl65, width: 111"]2[/TD]
[TD="class: xl65, width: 111"]Thunder[/TD]
[TD="class: xl65, width: 111"]30[/TD]
[TD="class: xl66"]John[/TD]

[TD="class: xl66"]3[/TD]
[TD="class: xl66"][/TD]

[TD="class: xl65, width: 111"]Wick[/TD]

[TD="class: xl65, width: 111"]4
[/TD]

[TD="class: xl65, width: 111"][Wick][/TD]

</tbody>

[TABLE="width: 508"]
<colgroup><col span="4"><col></colgroup><tbody>[TR]
[TD="colspan: 2"]Desired Outcome[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]AI[/TD]
[TD]AJ[/TD]
[TD]AK[/TD]
[TD]AL[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Nickname[/TD]
[TD]Age[/TD]
[TD]Name[/TD]
[TD]Surname[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Thunder[/TD]
[TD]30[/TD]
[TD]John[/TD]
[TD]Wick[/TD]
[/TR]
</tbody>[/TABLE]


B. There are also cases where the AK2 cell is completely blank so in this occassion the line has to be erased because if there is no name there is no reason this libe to exist.

<colgroup><col style="mso-width-source:userset;mso-width-alt:4059; width:83pt" width="111" span="4"> </colgroup><tbody>
[TD="width: 111"]Case C
[/TD]
[TD="width: 111"][/TD]
[TD="width: 111"][/TD]
[TD="width: 111"][/TD]

[TD="class: xl65, width: 111"][/TD]
[TD="class: xl65, width: 111"]AI[/TD]
[TD="class: xl65, width: 111"]AJ[/TD]
[TD="class: xl65, width: 111"]AK[/TD]

[TD="class: xl65, width: 111"]1[/TD]
[TD="class: xl65, width: 111"]Nickname[/TD]
[TD="class: xl65, width: 111"]Age[/TD]
[TD="class: xl65, width: 111"]Name[/TD]

[TD="class: xl65, width: 111"]2[/TD]
[TD="class: xl65, width: 111"]Thunder[/TD]
[TD="class: xl65, width: 111"]30[/TD]
[TD="class: xl66"][/TD]

</tbody>

[TABLE="width: 444"]
<colgroup><col span="4"></colgroup><tbody>[TR]
[TD]Desired outcome[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]AI[/TD]
[TD]AJ[/TD]
[TD]AK[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Nickname[/TD]
[TD]Age[/TD]
[TD]Name[/TD]
[/TR]
</tbody>[/TABLE]


I hope everything is clear no and sorry for my english but i am not a native speaker...

 

[Peter_SSs]

Thanks, that is clearer. However, you haven't answered my question:

Then ideally we need to know a column that can safely be used to determine the last row of data in the sheet. I had been using column AK, but if that can be blank it would not be reliable. Is there any column that always has data in every row?

 

[Pettor]

Really sorry, I missed that.

Yes the column that has always data is the column H. Is this ok or you need the last? THe last should be the L.

Thanks!

 

[Peter_SSs]

Yes the column that has always data is the column H.

OK, provided column H has something in it even for rows like Case B rows 3 & 4 and Case C row 2 then try this version

Sub MoveNameDeleteRow_v3()
  Dim r As Long
  
  Application.ScreenUpdating = False
  For r = Range("H" & Rows.Count).End(xlUp).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
  Application.ScreenUpdating = True
End Sub

 

[Pettor]

It is almost perfect. The ony problem is that it doens't do so in the last row of the data.
The cell AK2 of the bottom row is not moved to the AL1 of the previous row.

 

[Peter_SSs]

It is almost perfect. The ony problem is that it doens't do so in the last row of the data.
The cell AK2 of the bottom row is not moved to the AL1 of the previous row.

Then I'm guessing this was not quite correct?

Yes the column that has always data is the column H.

For the data where the bottom row was not moved, is there something in column H of that bottom row?

 

[Pettor]

No, the line where the AK data that have to be moved are, is completely blank besides this AK cell.
This line is created because of the existance of this cell. That is why I want to move it to the upper line and erase it completely.

 

[Peter_SSs]

No, the line where the AK data that have to be moved are, is completely blank besides this AK cell.
This line is created because of the existance of this cell. That is why I want to move it to the upper line and erase it completely.

OK, so back to my question about whether there is a column that always has data so we can reliably tell where the end of the data is, it seems like the actual answer to that is "No".

In that case, try this instead.

Sub MoveNameDeleteRow_v4()
  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
  Application.ScreenUpdating = True
End Sub

 

[Pettor]

Sorry, I thought you were saying about the upper row... Now it is perfect!
Case solved, so let's move to the next one...

Your contribution is amazing!

Thanks again and have a very nice Sunday!

 

[Peter_SSs]

You're welcome. Glad we got there in the end.