Deleting Lines with a Macro Command

Pettor

Board Regular
Joined
Aug 8, 2015
Messages
175
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!

NicknameAgeName
Thunder30John
Wick

<tbody>
</tbody>
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Give this a try in a copy of your workbook.

Rich (BB code):
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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.

Rich (BB code):
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
 
Upvote 0
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!
 
Upvote 0
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
AIAJAK
1NicknameAgeName
2Thunder30
3Wick
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?
 
Upvote 0
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
AI
AJ
AK
1
NicknameAgeName
2Thunder30Wick
3 [Wick1]

<colgroup><col style="width:48pt" width="64" span="4"> </colgroup><tbody>
</tbody>

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


AIAJAK
1NicknameAgeName
2Thunder30

<colgroup><col style="width:48pt" width="64" span="4"> </colgroup><tbody>
</tbody>
Thanks!
 
Upvote 0
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
AI
AJ
AK
1
NicknameAgeName
2Thunder30Wick
3 [Wick1]

<colgroup><col style="width:48pt" width="64" span="4"> </colgroup><tbody>
</tbody>
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?
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top