I have a dataset that is 80 columns by 200,000 rows. This data contains cell values that have beginning or trailing spaces. I need to identify which cells have those spaces.
我有一个80列乘200,000行的数据集。此数据包含具有开始或尾随空格的单元格值。我需要确定哪些单元格具有这些空格。
If the dataset were smaller I could easily write a couple of for loops to check each cell for spaces. However, considering the size of the data, checking each cell would slow excel down (if not crash it). I anticipate that auto filter would end the same way.
如果数据集较小,我可以轻松编写几个for循环来检查每个单元格的空格。但是,考虑到数据的大小,检查每个单元格会减慢速度(如果没有崩溃)。我预计自动过滤器会以同样的方式结束。
Is there any way to efficiently check the dataset for spaces at the beginning or end of a cell value, without going cell by cell via loop
有没有办法有效地检查数据集中单元格值开头或结尾的空格,而不是逐个单元格循环
3 个解决方案
#1
1
Private Sub this()
Dim i As Long, j As Long
Dim sh As Worksheet
Dim sheetArr As Variant
Set sh = ThisWorkbook.Sheets("Sheet1")
sheetArr = sh.UsedRange
rowC = sh.UsedRange.Rows.Count
colC = sh.UsedRange.Columns.Count
For i = 1 To rowC
For j = 1 To colC
If Left(sheetArr(i, j), 1) = " " Then
sh.Cells(i, j).Interior.ColorIndex = 37
End If
If Right(sheetArr(i, j), 1) = " " Then
sh.Cells(i, j).Interior.ColorIndex = 37
End If
Next j
Next i
End Sub
I tested this on a 1 million row sheet and was done within 5 seconds. This should be good enough.
我在100万行表上测试了这个,并在5秒内完成。这应该足够好了。
#2
2
1) Select all cells you want to use
1)选择要使用的所有单元格
2) Click on Home -> Condition formatting button -> New Rule
2)单击主页 - >条件格式按钮 - >新规则
3) Scroll down to "Use a formula to determine which cells to format"
3)向下滚动到“使用公式确定要格式化的单元格”
4) Assuming your current cell is A1, type in the followiing
4)假设您当前的单元格为A1,请键入以下内容
=OR((RIGHT(A1,1)=" "),(LEFT(A1,1)=" "))
5) Press format button, click on "Fill" an choose a color for fill
5)按格式按钮,点击“填充”,选择一种颜色进行填充
6) Press "ok", "ok".
6)按“确定”,“确定”。
The cells should be highlighted now
现在应该突出显示单元格
#3
0
A loop might be slow if you query the workbook cell by cell. Instead, a loop querying a variant array will be much much faster:
如果逐个单元查询工作簿,则循环可能会很慢。相反,查询变量数组的循环会快得多:
Dim i as long
Dim j as long
Dim a() as Variant
a = yourrange.value
for i = lbound(a,1) to ubound(a,1)
for j = lbound(a,2) to ubound(a,2)
if left(a(i,j),1) = " " or right(a(i,j),1) = " " then
'beginning or ending with " "
end if
next j
next i
Another option would be to copy/paste the data to notepad++ for example and then use the extended search function with "\t " and " \t" as well as "\r\n " and " \r\n" in order to find " " next to tabs and breaks which separat columns and rows respectively
另一个选择是将数据复制/粘贴到记事本++,然后使用扩展搜索功能“\ t”和“\ t”以及“\ r \ n”和“\ r \ n”以便在标签和分隔符旁边找到“”,分别分隔列和行
#1
1
Private Sub this()
Dim i As Long, j As Long
Dim sh As Worksheet
Dim sheetArr As Variant
Set sh = ThisWorkbook.Sheets("Sheet1")
sheetArr = sh.UsedRange
rowC = sh.UsedRange.Rows.Count
colC = sh.UsedRange.Columns.Count
For i = 1 To rowC
For j = 1 To colC
If Left(sheetArr(i, j), 1) = " " Then
sh.Cells(i, j).Interior.ColorIndex = 37
End If
If Right(sheetArr(i, j), 1) = " " Then
sh.Cells(i, j).Interior.ColorIndex = 37
End If
Next j
Next i
End Sub
I tested this on a 1 million row sheet and was done within 5 seconds. This should be good enough.
我在100万行表上测试了这个,并在5秒内完成。这应该足够好了。
#2
2
1) Select all cells you want to use
1)选择要使用的所有单元格
2) Click on Home -> Condition formatting button -> New Rule
2)单击主页 - >条件格式按钮 - >新规则
3) Scroll down to "Use a formula to determine which cells to format"
3)向下滚动到“使用公式确定要格式化的单元格”
4) Assuming your current cell is A1, type in the followiing
4)假设您当前的单元格为A1,请键入以下内容
=OR((RIGHT(A1,1)=" "),(LEFT(A1,1)=" "))
5) Press format button, click on "Fill" an choose a color for fill
5)按格式按钮,点击“填充”,选择一种颜色进行填充
6) Press "ok", "ok".
6)按“确定”,“确定”。
The cells should be highlighted now
现在应该突出显示单元格
#3
0
A loop might be slow if you query the workbook cell by cell. Instead, a loop querying a variant array will be much much faster:
如果逐个单元查询工作簿,则循环可能会很慢。相反,查询变量数组的循环会快得多:
Dim i as long
Dim j as long
Dim a() as Variant
a = yourrange.value
for i = lbound(a,1) to ubound(a,1)
for j = lbound(a,2) to ubound(a,2)
if left(a(i,j),1) = " " or right(a(i,j),1) = " " then
'beginning or ending with " "
end if
next j
next i
Another option would be to copy/paste the data to notepad++ for example and then use the extended search function with "\t " and " \t" as well as "\r\n " and " \r\n" in order to find " " next to tabs and breaks which separat columns and rows respectively
另一个选择是将数据复制/粘贴到记事本++,然后使用扩展搜索功能“\ t”和“\ t”以及“\ r \ n”和“\ r \ n”以便在标签和分隔符旁边找到“”,分别分隔列和行