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>
 
Hi Peter,

I have a couple more questions if you don't mind.

After searching the data I realized 3 more needs.

A. Columns I & K have some dots and commas that have to be erased when executing the macro. Instead of "John, Wick" I have to get the "John Wick" and the same for the dots.
B. Columns I & K have some lower case letters and ideally I would like to have everything in Upper case to keep a similar format. Instead of "John Wick" to get "JOHN WICK"
C. Columns H, J & L have some negative numbers that are not allowed. I must have only positive numbers, is it possible the commant to delete the data of a cell when there is a negative value? I don't mind it to be blank after that.
D. Columns H,J, L, M, N, O, P, Q, R, S, T, U, V, W, X, Y, Z, AA, AB, AC, AD, AE, AF, AG, AH, AI, AJ, have numerical data and when we replaced the dot with the comma they are still in a text format. Is it possible these to be transformed in a numerical format so as to be able to execute calculations?

Many thanks in advance.
 
Upvote 0

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
There is quite a bit of 'extra' here and I am about to be away for some days and will probably have little or no access to the forum. Still, some things need clarification so that if not resolved by my return I do understand better.


A. Columns I & K have some dots and commas that have to be erased when executing the macro. Instead of "John, Wick" I have to get the "John Wick" and the same for the dots.
This is somewhat confusing. In post 21 you asked that for the whole sheet dots be replaced with commas. Are these commas in columns I & K dots that became commas because of the code modification in post 23 or were they already commas from the start?



B. Columns I & K have some lower case letters and ideally I would like to have everything in Upper case to keep a similar format. Instead of "John Wick" to get "JOHN WICK"
That one sounds straight-forward enough.


C. Columns H, J & L have some negative numbers that are not allowed. I must have only positive numbers, is it possible the commant to delete the data of a cell when there is a negative value? I don't mind it to be blank after that.
I'm not sure what that blue text is supposed to mean. Are we just clearing cells that have negative numbers? presumably we won't know they are numbers until point D below is resolved or can we just say that if the text value (before point D is done) starts with a "-" then clear the cell?


D. Columns H,J, L, M, N, O, P, Q, R, S, T, U, V, W, X, Y, Z, AA, AB, AC, AD, AE, AF, AG, AH, AI, AJ, have numerical data and when we replaced the dot with the comma they are still in a text format. Is it possible these to be transformed in a numerical format so as to be able to execute calculations? [/QUOTE]Is this what the post 21 'replace dot with comma' request was about. Normally numbers don't contain the thousands separator itself but that is applied by formatting the cell appropriately. Should post 21 have asked to replace dot with nothing instead?
 
Upvote 0
Hi Peter,

Sorry for the confusion but some things can be seen after the execution of the code as there are thousands of rows.

A. You are right. I was trying to understand why these commas occurred in some records in these columns and obviously these were dots that were replaced... What I understand now is that in these columns that have text data, the dot should not be replaced in the first place, or replaced again, from commas to dots, if this is more convenient for the code.

B. All clear

C. "is it possible the command/code to delete the data". Yes, we have to clear the cells that have negative numbers in these columns and yes, since this is before point D we have to delete the cell values when they start with the "-" sign.

D. Yes, this is what post 21 was referring to. When I retrieve the data from the webpage they should be a number but in my country, the decimal sign is the comma, so I need them to be transformed to numbers by replacing the dot with comma and change also the cells' formating. If we can succeed that by only replacing the format without replacing the dot is welcomed of course.


I hope all these help!


Once again, thank you so much, your help is priceless!
 
Upvote 0
Thanks, that does add some clarity though on closer inspection there still is some confusion for me about what is in the various columns. For example, previously column AI contained nicknames and now in post 31 you say that column contains numerical values. Perhaps the column contains both?

In any case, see if you can make something out of this sort of code. The early part of the code is what we had before & I have commented the parts where I have tried to address the post 31 issues.
The code is untested as I do not have any realistic data to do any tests on.

Rich (BB code):
Sub MoveNameDeleteRow_v6()
  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("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
  
  'Take any commas or dots out of the names in column I & ensure upper case
  With Range("I2", Range("I" & Rows.Count).End(xlUp))
    .Replace What:=".", Replacement:="", LookAt:=xlPart
    .Replace What:=",", Replacement:="", LookAt:=xlPart
    .Value = Evaluate(Replace("if(#="""","""",upper(#))", "#", .Address))
  End With
  
  'Take any commas or dots out of the names in column K & ensure upper case
  With Range("K2", Range("K" & Rows.Count).End(xlUp))
    .Replace What:=".", Replacement:="", LookAt:=xlPart
    .Replace What:=",", Replacement:="", LookAt:=xlPart
    .Value = Evaluate(Replace("if(#="""","""",upper(#))", "#", .Address))
  End With
  
  'In columns H, J & L clear any cells that begin with a "-"
  With Intersect(ActiveSheet.UsedRange, Range("H:H, J:J, L:L"))
    .Replace What:="-*", Replacement:="", LookAt:=xlWhole
  End With
  
  'Change dots to commas in numerical columns, format the columns and convert text numbers to actual numbers
  With Intersect(ActiveSheet.UsedRange, Range("H:H, J:J, L:AJ"))
    .Replace What:=".", Replacement:=",", LookAt:=xlPart
    .NumberFormat = "General"
    .Value = .Value
  End With
  
  Application.ScreenUpdating = True
End Sub
 
Upvote 0
Hi Peter,

I just saw your answer as there was no notification for some reason. I will test and get back to you.

Many many thanks again!
 
Upvote 0
Hi Peter,

Let's get things from the beginning as the code became very complicated (for me at least) and it breaks the file instead or making it better.

First of all, please find the excel file on mediafire in the below link. There are two sheets. The first (databefore) is how I receive data from the online source and the second (dataafter) is the desired format I want to get after applying the code.

I didn't want to explain all these from the beginning that is why I tried to simplify the process with a shorter description than the actual, but let me explain in detail now as it will obviously make things easier.

https://www.mediafire.com/file/fzj4enlprthiyzj/Data.xlsx/file



Let me explain what every columns has to be/do:

A,B,C,D,E,G,AK = (have to remain untouched. Column B has to be in Date format and Column D has to be in Time format)

F = Text format (has to remain as it is and erase the "-" where it exists)

H,J,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,AA,AB,AC,AD,AE,AF,AG,AH,AI,AJ = Numerical data (Have to replace the "." with ",", erase the "-" where it exists and erase also the negative values. They have also to be transformed to numerical data instead of text)

I,K = Text values (Have to make all letters in Upper Case and leave everything as it is)

AL,AM = (This is the most tricky part that we have already solved. The data in brackets is the score of the first half, and the data below in the otherwise empty line is the score of the second half of the games. So, the data of the AL2 when the rest of the line is empty, have to go to the AM1 field and the empty data has to be deleted. Of course we have also to remove the brackets from the AL column. When the AL column is blank the line has to remain as it is as it is a game that has not yet been completed)

The first line has of course always to remain untouched as it contains the headings.


----------------------------------------------------------------------------------------------------------------------

The initial code below, solves the issue in the AL & AM columns but since it does the replacements in all columns (red code) it needs to be corrected to avoid changes in the I & K columns.

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 Cells(r, "AL").Value Like "[[]*]" Then
Rows(r).Delete
ElseIf IsEmpty(Cells(r, "AK").Value) Then
Cells(r - 1, "AM").Value = Cells(r, "AL").Value
Rows(r).Delete
End If
Next r

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


With ActiveSheet.UsedRange
.Replace What:=".", Replacement:=",", LookAt:=xlPart
.Replace What:="-", Replacement:="*", LookAt:=xlWhole
End With


Application.ScreenUpdating = True
End Sub

--------------------------------------------------------------------------------------------------------------------------------------------

I tried to make the below corrections, but they didn't seem to work. If I delete the code in red, It does work, but the numerical data are still in text format that has to be changed...

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 Cells(r, "AL").Value Like "[[]*]" Then
Rows(r).Delete
ElseIf IsEmpty(Cells(r, "AK").Value) Then
Cells(r - 1, "AM").Value = Cells(r, "AL").Value
Rows(r).Delete
End If
Next r

'Removes the () symbols

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

'Changes the "-" with ""

With Intersect(ActiveSheet.UsedRange, Range("F:F, H:H, J:J, L:AJ"))
.Replace What:=".", Replacement:=",", LookAt:=xlPart
.Replace What:="-", Replacement:="", LookAt:=xlWhole
.NumberFormat = "General"
.Value = .Value

End With

'In column I ensure upper case


With Range("I2", Range("I" & Rows.Count).End(xlUp))
.Value = Evaluate(Replace("if(#="""","""",upper(#))", "#", .Address))
End With

'In column K ensure upper case

With Range("K2", Range("K" & Rows.Count).End(xlUp))
.Value = Evaluate(Replace("if(#="""","""",upper(#))", "#", .Address))
End With


Application.ScreenUpdating = True
End Sub

-------------------------------------------------------------------------------------------------------------------------

Please let me know if everything is clear and I will try also to make some amendments by myself in case I make it...
 
Upvote 0
I'm afraid that this has become too complex & I have not been able to get my head back around it.
 
Upvote 0
Dear Peter good morning,

Finally, after so long, I managed to understand how the code works and it does the job perfectly.
Your help was amazing but I had to somehow get familiar with VBA to understand how it works. I just have one more question.
In column F there are some cells with the "-" value and the code gets them deleted.
Is there any way that we could add an additional macro that adds, to the blank cells only, a numbering from 1-99 (starting from the first row and moving to the others below) that also restarts when the date (column B) changes?

Something like the below?

Column BColumn F
DATECODE
08/02/201
08/02/202
09/02/201
09/02/202
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

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