excel - 从特定工作表中删除 Excel 中的重复行
问题描述
提前感谢您的帮助!
我目前正在使用以下代码将多个 .csv 文件填充到一张表中,然后隐藏该表。我需要的帮助是从该工作表中删除重复的行。它可以合并到此代码中吗?谢谢!
Sub ImportCSVsWithReference()
'UpdatedforSPSS
Dim xSht As Worksheet
Dim xWb As Workbook
Dim xStrPath As String
Dim xFileDialog As FileDialog
Dim xFile As String
On Error GoTo ErrHandler
Set xFileDialog = Application.FileDialog(msoFileDialogFolderPicker)
xFileDialog.AllowMultiSelect = False
xFileDialog.Title = "Select the folder with the csv files [File Picker]"
If xFileDialog.Show = -1 Then
xStrPath = xFileDialog.SelectedItems(1)
End If
If xStrPath = "" Then Exit Sub
Set xSht = Sheets.Add
ActiveSheet.Name = "ImportedData"
Worksheets("ImportedData").Visible = False
Application.ScreenUpdating = False
xFile = Dir(xStrPath & "\" & "*.csv")
Do While xFile <> ""
Set xWb = Workbooks.Open(xStrPath & "\" & xFile)
ActiveSheet.UsedRange.Copy xSht.Range("A" & Rows.Count).End(xlUp).Offset(1)
xWb.Close False
xFile = Dir
Loop
Application.ScreenUpdating = True
Exit Sub
ErrHandler:
MsgBox "Encountered an error. Try again", , "Error"
End Sub
解决方案
实际上有一个内置函数可以从范围中删除重复项。它被称为RemoveDuplicates
...
让我们看一个例子。我在这里假设-
- 该表有 3 列
- 该表有 100 行
- 表格没有标题行
然后删除重复项的代码将如下所示:
ActiveSheet.Range("A1:C100").RemoveDuplicates Columns:=Array(1, 2, 3), Header:=xlNo
请参阅https://docs.microsoft.com/en-us/office/vba/api/excel.range.removeduplicates上的文档
推荐阅读
- amazon-web-services - 使用 AWS CDK,如何在代码管道中为源代码设置 oathToken,以从 GitHub 中提取源代码,而不使用 Secret Manager 服务?
- spring-boot - How to display all processor in graphical view?
- jquery - 仅从已编辑的行中获取表行值
- php - Passing everything to a base class is good if only children needs that data?
- oracle-sqldeveloper - 使用 SQL Developer 将带有日期时间的 excel 导入 Oracle DB 时出现“Invalid Mounth”
- python - cursor.execute 返回 1 个整数
- azure - Access Azure File Storage from on-premise machine via virtual network that is connected with virtual network gateway (site-to-site connection)
- gremlin - 在 Gremlin 中添加或更新边
- javascript - 针对 iframe 的内容脚本不会在特定网站上触发
- matlab - 具有二维 x 的 Fseminf 函数