vba 从范围加载的数组中删除空白条目
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25083868/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Remove blank entries from an array loaded by a range
提问by ImmutableTuple
I am trying to delete blank entries from an array that was loaded from a field called TY[L3 Name] (1 column, X rows long) from a data table in excel. The below code is intended to remove all blank values from the array (once it has been loaded with the range), and return a new array with rows that only have data in them. I will want to pass this array onto a collection later to remove duplicates, but I am trying to figure out why I can't get ride of the blanks first (now I am at a point where I just want to understand how to do this regardless if i pass this onto something else or not).
我试图从一个数组中删除空白条目，该数组是从 excel 中的数据表中名为 TY[L3 Name]（1 列，X 行长）的字段加载的。下面的代码旨在从数组中删除所有空白值（一旦它与范围一起加载），并返回一个新数组，其中包含只有数据的行。我想稍后将此数组传递到集合中以删除重复项，但我想弄清楚为什么我不能先摆脱空白（现在我只想了解如何做到这一点无论我是否将其传递给其他东西）。
The code errors out at the ReDim Preserve line. I first sized the NewArr to the MyArr table, but had blank rows returned at the end. I then tried to resize it so I only had rows with data in them, but I cannot seem to get the NewArr() array to do this without an error.
代码在 ReDim Preserve 行出错。我首先将 NewArr 的大小调整为 MyArr 表，但最后返回了空白行。然后我尝试调整它的大小，所以我只有其中有数据的行，但我似乎无法让 NewArr() 数组在没有错误的情况下执行此操作。
I am using the immediate window to verify that there are no blank entries (currently 8 rows at the end of the TY[L3 Name] range).
我正在使用即时窗口来验证没有空白条目（当前 TY[L3 Name] 范围末尾的 8 行）。
Sub BuildArray()
' Load array
Dim MyArr()
Dim j As Long
' Size array
MyArr() = Range("TY[L3 Number]")
ReDim NewArr(LBound(MyArr) To UBound(MyArr), 1)
' For Loop to search for Blanks and remove from Array
' The Lbound and UBound parameters will be defined by the size of the TY[L3 Number] field in the TY Table
For i = LBound(MyArr) To UBound(MyArr)
If MyArr(i, 1) <> "" Then
j = j + 1
NewArr(j, 1) = MyArr(i, 1)
End If
Next i
ReDim Preserve NewArr(1 To j, 1) 'Error out here; "Subscript out of range." Can't seem to get this Array to new size without blank entries.
' Debug Window to show results of revised array.
Dim c As Long
For c = LBound(NewArr) To UBound(NewArr)
Debug.Print NewArr(c, 1)
Next
Debug.Print "End of List"
End Sub
回答by Dan Wagner
Working through arrays can be tricky in VBA, but I think the example below will show you how a different strategy for populating the "No Blanks" Arraymight be work:
在 VBA 中处理数组可能会很棘手，但我认为下面的示例将向您展示填充“无空白”的不同策略是如何Array工作的：
Suppose we start off with a single Worksheet, with the CoolRangenamed as shown:
假设我们从单个 开始Worksheet，CoolRange名称如下所示：
Generating an array without blanks could be done like this:
可以像这样生成一个没有空格的数组：
Option Explicit
Sub BuildArrayWithoutBlanks()
Dim AryFromRange() As Variant, AryNoBlanks() As Variant
Dim Counter As Long, NoBlankSize As Long
'set references and initialize up-front
ReDim AryNoBlanks(0 To 0)
NoBlankSize = 0
'load the range into array
AryFromRange = ThisWorkbook.Names("CoolRange").RefersToRange
'loop through the array from the range, adding
'to the no-blank array as we go
For Counter = LBound(AryFromRange) To UBound(AryFromRange)
If AryFromRange(Counter, 1) <> "" Then
NoBlankSize = NoBlankSize + 1
AryNoBlanks(UBound(AryNoBlanks)) = AryFromRange(Counter, 1)
ReDim Preserve AryNoBlanks(0 To UBound(AryNoBlanks) + 1)
End If
Next Counter
'remove that pesky empty array field at the end
If UBound(AryNoBlanks) > 0 Then
ReDim Preserve AryNoBlanks(0 To UBound(AryNoBlanks) - 1)
End If
'debug for reference
For Counter = LBound(AryNoBlanks) To UBound(AryNoBlanks)
Debug.Print (AryNoBlanks(Counter))
Next Counter
Debug.Print "End of List"
End Sub
So, to summarize, we:
所以，总而言之，我们：
- Create a 1-D array for our eventual array with blanks removed
- Iterate through our original array (with blanks)
- Unless the array field is blank, we increase our non-blank counter, then add the value to the non-blank array, then expand the non-blank array
- Blow away the last pesky empty field in our non-blank array
- 为我们的最终数组创建一个一维数组，删除空白
- 遍历我们的原始数组（带空格）
- 除非数组字段为空，否则我们增加非空计数器，然后将值添加到非空数组，然后扩展非空数组
- 吹走我们非空数组中最后一个讨厌的空字段
From your problem description, it sounds like you'll eventually be stripping away duplicates with a Collection-- love it. Out of curiosity, what will you use the non-blank-but-with-duplicates array for?
从您的问题描述来看，听起来您最终会用Collection-- 喜欢它来去除重复项。出于好奇，您将使用 non-blank-but-with-duplicates 数组做什么？
