Excel VBA:End(xlUp)和End(xlDown)都在第244行结束，该行为空

Question

我用公式填充了B2:GQ244，复制了范围并按值粘贴，然后逐列对范围进行排序。B8:GQ244中的细胞均为空白细胞。然后，我想从第2行开始，逐列连接非空白单元格。为此，我需要在每列中找到最后一个非空白单元格。

出于某种原因，End(xlUp(和End(xlDown(都给出了空的行244。我不明白为什么。我认为文件可能已损坏。所以，我把这两张表和模块复制到一个新创建的工作簿中，但没有用。为什么End(xlUp(和End(xlDown(都给出第244行？

.Range("B2:GQ244").Formula = "=IF(ISERROR(FIND( B$1,Sheet9!$H34)),"""",Sheet9!$I34)"
'paste by value to get rid of formulae
.Range("B2:GQ244").Copy
.Range("B2").PasteSpecial Paste:=xlPasteValues
'sort by column
Dim last_row As Long
Dim j As Long
For i = 2 To 200 Step 1
Range(.Cells(2, i), .Cells(245, i)).Sort key1:=.Cells(2, i), order1:=xlAscending
Next i
For i = 2 To 200 Step 1
last_row = .Cells(65536, i).End(xlUp).Row
last_row = .Cells(1, i).End(xlDown).Row

Answer 1

下面的代码将删除列底部的所有空字符串以及包含零的字符串。

Sub ClearBlankCells()
' 146

Dim Rng         As Range            ' working range
Dim R           As Long             ' intermediate: row
Dim C           As Long             ' loop counter: columns
Application.ScreenUpdating = False
With ActiveSheet
With .Range("B2:GQ244")
.Formula = "=IF(ISERROR(FIND( B$1,Sheet9!$H34)),"""",Sheet9!$I34)"
' replace formulas with their values
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
Application.CutCopyMode = False
For C = 2 To 200 Step 1
Set Rng = .Columns(C)
R = Application.Evaluate("SUMPRODUCT((" & Rng.Address & "<>"""")*(" & _
Rng.Address & "<>0)*1)")
If R > 0 Then
Set Rng = Range(.Cells(R + 1, C), .Cells(Rows.Count, C))
Rng.ClearContents
End If
' sort by column
'            Range(.Cells(2, C), .Cells(245, C)).Sort Key1:=.Cells(2, C), Order1:=xlAscending
Next C
End With
Application.ScreenUpdating = True
End Sub

请注意，每列底部上方的数据块(包括标题(中不得包含空格或零。

排序必须在删除这些单元格后进行，但我将排序指令留空，因为它在语法或概念上都是错误的。如果需要对每一列进行排序，则语法是错误的，因为语法会对整个工作表进行排序。另一方面，如果你想对整张纸进行排序，你不必循环200次。

代码运行非常缓慢，这导致了两个观察结果。

1. 它99%的时间都在修复它在第一行中造成的伤害
2. 它关注的数据范围远远大于实际合理要求的数据范围。没有人愿意看一张200列244行的纸

因此，必须有更好的方法来实现你想要的。

Answer 2

我无法确认您的发现。有了一个空白的ActiveSheet和一个空白Sheet9，下面的代码用零B2:GQ244填充了ActiveSheet。然后读取最后一行xlUp为244，xlDown为2。这两个值都是预期的。也许您有一个禁止显示零的设置。然而，正如我在上面的评论中所解释的，一个显示为空的单元格不一定是空的，这也适用于包含由公式插入的NullString的单元格，即使随后删除了公式，将空字符串保留在其位置。

Sub Examine()

Dim last_row As Long
Dim i As Long
With ActiveSheet
.Range("B2:GQ244").Formula = "=IF(ISERROR(FIND( B$1,Sheet9!$H34)),"""",Sheet9!$I34)"

'paste by value to get rid of formulae

.Range("B2:GQ244").Copy
.Range("B2").PasteSpecial Paste:=xlPasteValues

'sort by column

For i = 2 To 200 Step 1
Range(.Cells(2, i), .Cells(245, i)).Sort Key1:=.Cells(2, i), Order1:=xlAscending
last_row = .Cells(.Rows.Count, i).End(xlUp).Row
Debug.Print last_row            ' returns 244
last_row = .Cells(1, i).End(xlDown).Row
Debug.Print last_row            ' returns 2
Next i
End With
End Sub

因此，剩下的唯一谜团是，为什么.Cells(1, i).End(xlDown).Row会给你一个244的值。事实并非如此。因此，解决方案必须在测试中进行，而不是在测试结果中。将你的测试方法与我上面使用的方法进行比较。