VBA：如何识别在单元格值的开头或结尾处有空格的单元格

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

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

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

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

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