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]

 

[Peter_SSs]

Give this a try in a copy of your workbook.

Sub MoveNameDeleteRow()
  Dim r As Long
  
  Application.ScreenUpdating = False
  For r = Range("C" & Rows.Count).End(xlUp).Row To 2 Step -1
    If IsEmpty(Cells(r, 1).Value) Then
      Cells(r - 1, 3).Value = Cells(r - 1, 3).Value & " " & Cells(r, 3).Value
      Rows(r).Delete
    End If
  Next r
  Application.ScreenUpdating = True
End Sub

 

[Pettor]

Hi Petter,
Thank you for your reply. Unfortunately it doesn't seem to work as it deletes both the empty and the right lines.
My column with the problem is the AK and although I replaced it in your code something strange happens as it gathers also all the copied values in the "C3" cell.

 

[Peter_SSs]

Your sample showed 3 columns of data. Can you confirm which 3 they actually are on the worksheet? In the absence of any information about that I assumed columns A:C.

Suggest also you try the code with some sample data in A:C and see how it goes.

 

[Pettor]

I am really sorry for the confusion, I tired to simplify the process but it is not how it works obviously in programming...

Columns are A to AK and double line is created in the AK column.

Indeed when I tried in 3 columns it worked, the only problem is that I want the data that will be copied to be entered in separate cell next to the AK column (createing a cell in the AL column). In your code it was pasted in the same cell with "name".

THanks again, your help is very appreciated!

 

[Peter_SSs]

Columns are A to AK and double line is created in the AK column.

.. I want the data that will be copied to be entered in separate cell next to the AK column (createing a cell in the AL column).

Thanks for the clarifications.

See if this works better (& is easier to interpret).
I have assumed that the AJ column has data in every row except the rows that have dropped the name to the next row. If that is not the case then the AJ in the code would need to be changed to a column that does have data in all the rows that should be kept.

Sub MoveNameDeleteRow_v2()
  Dim r As Long
  
  Application.ScreenUpdating = False
  For r = Range("AK" & Rows.Count).End(xlUp).Row To 2 Step -1
    If 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]

I think it works prerfectly fine! Thanks you so much! I wish I had the time to learn all this code.

Just another question, is there any way also to delete the entire first line (that with the many data) when there is no value in the cells of the AK column?
There are times that the name doesn't exist at all so I want to delete the whole first line beacuse it an unwanted record. The same happens also when the name in a cell is in []. Of course all of these cases happens in the AK column.

Thank you again so much!

 

[Peter_SSs]

I think it works prerfectly fine! Thanks you so much!

You're welcome. Glad it helped.

Just another question, is there any way also to delete the entire first line (that with the many data) when there is no value in the cells of the AK column?
There are times that the name doesn't exist at all so I want to delete the whole first line beacuse it an unwanted record. The same happens also when the name in a cell is in [].

This will be possible but needs some clarification.

If AK is blank, is it possible that anything is in AK in the row below? That is, could there be data like this and if so, clarify exactly what should happen to each row.

Excel Workbook
	AI	AJ	AK
1	Nickname	Age	Name
2	Thunder	30
3			Wick
Move name & delete row

Similar question really about the []. Could you give a couple of examples like you did in post 1 and again explain carefully what should happen?

 

[Pettor]

After executing the code I realized that the lines with the [] disappeared. These are very rare and are being created in a third line when something is not right. Something like the below table. But something in the code deletes them so it is not required anymore.

Example
[TABLE="width: 256"]
<colgroup><col style="width:48pt" width="64" span="4"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"]AI
[/TD]
[TD="class: xl65, width: 64"]AJ
[/TD]
[TD="class: xl69, width: 64"]AK[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]1
[/TD]
[TD="class: xl67, width: 64"]Nickname[/TD]
[TD="class: xl68, width: 64"]Age[/TD]
[TD="class: xl67, width: 64"]Name
[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]2[/TD]
[TD="class: xl67, width: 64"]Thunder[/TD]
[TD="class: xl68, width: 64"]30[/TD]
[TD="class: xl67, width: 64"]Wick[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]3[/TD]
[TD="class: xl67, width: 64"] [/TD]
[TD="class: xl67, width: 64"] [/TD]
[TD="class: xl67, width: 64"][Wick1]
[/TD]
[/TR]
</tbody>[/TABLE]

When the AK column is completely blank in the first row there is no way to have a second line with the rest of the data in the AK column. This is only being created when there is a surname in the field. So in this case the code has to identify the line with the blank cells in AK row and delete this entire first row.

Example

<colgroup><col style="width:48pt" width="64" span="4"> </colgroup><tbody>
[TD="class: xl65, width: 64"]
[/TD]
[TD="class: xl65, width: 64"]AI[/TD]
[TD="class: xl65, width: 64"]AJ[/TD]
[TD="class: xl65, width: 64"]AK
[/TD]

[TD="class: xl66, width: 64"]1[/TD]
[TD="class: xl67, width: 64"]Nickname[/TD]
[TD="class: xl68, width: 64"]Age[/TD]
[TD="class: xl67, width: 64"]Name[/TD]

[TD="class: xl66, width: 64"]2[/TD]
[TD="class: xl67, width: 64"]Thunder[/TD]
[TD="class: xl68, width: 64"]30[/TD]
[TD="class: xl67, width: 64"] [/TD]

[TD="class: xl66, width: 64"][/TD]
[TD="class: xl67, width: 64"] [/TD]
[TD="class: xl67, width: 64"] [/TD]
[TD="class: xl67, width: 64"] [/TD]

</tbody>

Thanks!

 

[Peter_SSs]

After executing the code I realized that the lines with the [] disappeared. These are very rare and are being created in a third line when something is not right. Something like the below table. But something in the code deletes them so it is not required anymore.

Example
[TABLE="width: 256"]
<colgroup><col style="width:48pt" width="64" span="4"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"]AI
[/TD]
[TD="class: xl65, width: 64"]AJ
[/TD]
[TD="class: xl69, width: 64"]AK[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]1
[/TD]
[TD="class: xl67, width: 64"]Nickname[/TD]
[TD="class: xl68, width: 64"]Age[/TD]
[TD="class: xl67, width: 64"]Name
[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]2[/TD]
[TD="class: xl67, width: 64"]Thunder[/TD]
[TD="class: xl68, width: 64"]30[/TD]
[TD="class: xl67, width: 64"]Wick[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]3[/TD]
[TD="class: xl67, width: 64"] [/TD]
[TD="class: xl67, width: 64"] [/TD]
[TD="class: xl67, width: 64"][Wick1]
[/TD]
[/TR]
</tbody>[/TABLE]

I'm not entirely sure what all that means in terms of exactly what should happen.
For the above sample, my code would move the [Wick1] up/right to cell AL2 and delete row 3. If that is not what you want, please clarify exactly what should happen with row 3 and row 2.

When the AK column is completely blank in the first row there is no way to have a second line with the rest of the data in the AK column. This is only being created when there is a surname in the field. So in this case the code has to identify the line with the blank cells in AK row and delete this entire first row.

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?