| I often encounter repeated data in Excel. If the number of data records is small, manual deletion may be acceptable. If the data size is large, you need to use a computer to pull the data. Generally, there are two methods, one is to use advanced filtering tools in Excel, and the other is as follows. Using VB Scripts may be easier to use. 1. function 1 Sub deletedouble () 'User input prompt
Dim userinput
Userinput = application. inputbox ("Enter the start row to be checked, end row, and column to be checked, in the format of, C ")
Dim arruserinput
Arruserinput = Split (userinput ,",")
Dim thecolumn
Thecolumn = ASC (arruserinput (2)-64 'column to be verified
Dim thestart
Thestart = arruserinput (0) 'Data start line
Dim theend
Theend = arruserinput (1) 'number of the last row of data.
Dim I 'number of each row
Dim J'
For I = thestart to theend' the process of loop processing. If the data is large, it may take a lot of time.
For J = I + 1 to theend' from the current row to the end.
If cells (I, thecolumn) = cells (J, thecolumn) then
Rows (j). Delete
End if
Next
Next
End sub 2. function 2 It is relatively convenient to use macros to solve such problems. If you are interested, try: 1. press Alt + F11 to open the VB Editor 2. Double-click thisworkbook in "MS Excel Object" in "engineering Resource Manager" on the left, andCodePaste the following code into the window: Sub deletes duplicate data ()
'Delete duplicate data in the col Column
'In this example, the duplicate data of column A (starting from cell A2) in the Excel table titled sheet1 is deleted.
Application. screenupdating = false
'You can modify the end values of the following three rows according to the actual situation.
Dim sheetscaption as string: sheetscaption = "sheet1"
Dim Col as string: Col = ""
Dim startrow as integer: startrow = 2 'Do not modify the following
Dim endrow as integer: endrow = sheets (sheetscaption). Range (COL & "65536"). End (xlup). Row
Dim count_1 as integer: count_1 = 0
Dim count_2 as integer: count_2 = 0
Dim I as integer: I = startrow With sheets (sheetscaption) Do
Count_1 = count_1 + 1
For J = startrow to I-1
If. Range (COL & I) =. Range (COL & J) then
Count_1 = count_1-1
. Range (COL & I). entirerow. Delete
Endrow = sheets (sheetscaption). Range (COL & "65536"). End (xlup). Row
I = I-1
Count_2 = count_2 + 1
Exit
End if
Next
I = I + 1
Loop while I <endrow + 1
End Msgbox "Total" & count_1 & "non-duplicate data"
Msgbox "delete" & count_2 & "duplicate data"
Application. screenupdating = true
End sub
|
