我有大约 40 个电子表格,每个电子表格最多包含 300k 行 x 93 列(当前)。大约有 11 亿个数据点。我需要检查每个单元格,并确定单元格是否包含 8 个特殊字符之一,这些字符在电子表格的导入中已被弄乱。
这是一项每天需要运行多次的任务以及许多其他步骤。因此,我正在寻找一种使用 VBA 来完成此操作的方法。我有以下代码:
Sub Hide_All_Sheets()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.DisplayStatusBar = False
Dim k As Integer
Dim t As String
Dim x As Integer
k = Sheets.Count
x = 1
While x <= k
t = Sheets(x).Name
If t = "Launch Screen" Or t = "Equiv sheet" Then
x = x + 1
ElseIf t = "Summary_1" And Worksheets("Launch Screen").Range("N5") = "1" Then
Sheets(x).Visible = True
x = x + 1
Else
Cells.Replace What:="ö", Replacement:=Chr(214), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="ü", Replacement:=Chr(220), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="ä", Replacement:=Chr(220), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="ß", Replacement:=Chr(223), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="è", Replacement:=Chr(200), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="Ü", Replacement:=Chr(223), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="Ä", Replacement:=Chr(223), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(x).Visible = False
x = x + 1
End If
Wend
End Sub
唯一的问题是加载过程从 20 秒变成了 900 秒。
我想知道有没有办法更快地做到这一点?特别是如果有一种方法可以运行手动 CTRL-H 过程,并在所有电子表格中进行替换,但使用 VBA?