VBA 中的 ToCol
Excel
-
以下公式调整为下面的截图。
-
在 Excel 中你可以这样做:
=TOCOL(B2:D11,1)
其中排除空单元格。
-
为了安全起见并排除所有空白单元格,您可以使用以下之一:
=LET(c,TOCOL(B2:D11),FILTER(c,c<>""))
=TOCOL(IF(B2:D11<>"",B2:D11,NA()),3)
-
如果您没有 Microsoft 365,您可以使用下面的 VBA 函数,如下所示:
=RangeToCol(B2:D11,1)
截图
- 下面截图的兴趣范围是
B2:D11
.
- 重要的是要了解白细胞是空白的但不是空的。您可能会遇到这样的单元格,尤其是当它们包含计算结果的公式时
=""
而且从具有此类单元格的范围复制数据并粘贴值时也是如此。
- Excel's
TOCOL
不认为它们是空白的,或者正如我所说,当第二个参数设置为 1 时,它仅排除空单元格(它们是空白单元格的一部分)。看专栏G
在屏幕截图中(ignore=1
).
- 相似地,
ISBLANK
实际上返回TRUE
仅适用于空单元格,就像COUNTA
计算所有非空单元格。
- 另一方面,
COUNTBLANK
“理解”空白单元格是什么。
- 研究屏幕截图的左下部分,以更好地了解其含义。
VBA
调用过程
Sub CopyToSingleColumns()
Const SRC_SHEET As String = "Sheet1"
Const SRC_FIRST_CELL As String = "A2"
Dim sCols(): sCols = VBA.Array("B:D", "E:G")
Const DST_SHEET As String = "Sheet1"
Dim dfCells(): dfCells = VBA.Array("J2", "K2")
Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
Dim sws As Worksheet: Set sws = wb.Sheets(SRC_SHEET)
Dim srg As Range
With sws.Range(SRC_FIRST_CELL)
Set srg = sws.Range( _
.Cells, sws.Cells(sws.Rows.Count, .Column).End(xlUp))
End With
Dim dws As Worksheet: Set dws = wb.Sheets(DST_SHEET)
Dim sData(), n As Long
For n = 0 To UBound(sCols)
sData = RangeToCol(srg.EntireRow.Columns(sCols(n)), 1)
dws.Range(dfCells(n)).Resize(UBound(sData)).Value = sData
Next n
MsgBox "Values copied to single columns.", vbInformation
End Sub
主要功能
- 相同的功能,但对于行,
RangeToRow
, 可以被找寻到here https://stackoverflow.com/a/75809065.
Function RangeToCol( _
ByVal rg As Range, _
Optional ByVal Ignore As Long = 0, _
Optional ByVal ScanByColumn As Boolean = False) _
As Variant
Dim srCount As Long: srCount = rg.Rows.Count
Dim scCount As Long: scCount = rg.Columns.Count
Dim drCount As Long: drCount = srCount * scCount
Dim sData()
If drCount = 1 Then
ReDim sData(1 To 1, 1 To 1): sData(1, 1) = rg.Value
Else
sData = rg.Value
End If
Dim dArr(): ReDim dArr(1 To drCount)
Dim sVal, sr As Long, sc As Long, dr As Long
If ScanByColumn Then
For sc = 1 To scCount
For sr = 1 To srCount
If IsErrorBlankTestPassed(sData(sr, sc), Ignore) Then
dr = dr + 1
dArr(dr) = sData(sr, sc)
End If
Next sr
Next sc
Else
For sr = 1 To srCount
For sc = 1 To scCount
If IsErrorBlankTestPassed(sData(sr, sc), Ignore) Then
dr = dr + 1
dArr(dr) = sData(sr, sc)
End If
Next sc
Next sr
End If
If drCount = 0 Then Exit Function ' only blanks and/or errors
Dim dData(): ReDim dData(1 To dr, 1 To 1)
For dr = 1 To dr
dData(dr, 1) = dArr(dr)
Next dr
RangeToCol = dData
End Function
辅助函数
Function IsErrorBlankTestPassed( _
ByVal Value As Variant, _
ByVal Ignore As Long) _
As Boolean
Dim IsAddable As Boolean
Select Case Ignore
Case 0: IsAddable = True ' nothing
Case 1: If Len(CStr(Value)) > 0 Then IsAddable = True ' blanks
Case 2: If Not IsError(Value) Then IsAddable = True ' errors
Case 3:
If Not IsError(Value) Then ' blanks and errors
If Len(CStr(Value)) > 0 Then IsAddable = True
End If
End Select
IsErrorBlankTestPassed = IsAddable
End Function