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>
 
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 "-"?


Case ACase B
AKAny Cell contains this calue
(1:1)-
OutcomeOutcome
1:1

<colgroup><col span="2"></colgroup><tbody>
</tbody>

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!
 
Upvote 0

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Untested, but try adding the blue code where shown.
Rich (BB code):
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
 
Upvote 0
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?
 
Upvote 0
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 "@". :oops:

After the various rows have been deleted, are all the values in AK (excluding the heading) surrounded by parentheses or only some?
 
Last edited:
Upvote 0
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

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

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