本帖最后由 青城山苦丁茶 于 2011-5-2 12:49 编辑
我们再来看看坛子中经常而且反复出现的一个问题:删除空行 对于这个问题,用技巧等等方式非常好解决,这里我们不去谈他,现在仅试图用VBA来解决看看 问题描述:如下图所示,当前表格中的B2至B25001区域中,有的地方是由函数产生的空(即假空),有的地方确实是没内容的真空(f2和f3两个单元格公式结果可看出这个情况),如何用VBA将B列的这些真空和假空所在的行整行删除呢? 先复习下录制宏:删除一行、连续的几行、不连续的几行的宏代码,经修改后的结果是: 删除单行:Rows(3).Delete 或 Range("3:3").Delete ’将第三行删除 删除连续几行:Rows("4:10").Delete或Range("4:10").Delete ’ 将第四至第十行删除 删除不连续几行:Range("8:8,12:15,23:33").Delete 还有以下形式: Range("B4").EntireRow.Delete’将B4所在的行删除 Range("B4:b10").EntireRow.Delete’将B4至B10所在的行删除 Range("B4,b6:b10").EntireRow.Delete’将B4和b6至b10所在的行删除 有了这些积木块后,再加上以前的循环、判断等等积木块,下面我们来实现删除空行的目的。 还是先来看看我们人工做这个活路时是如何来做的(贯穿始终的思想:编代码其实就是将我们的想法翻译成对应的VBA代码): 对B列单元格,从上往下一个一个地来看,只要他是为空,就删除该行,直到所有的都完成为止。 因此,很容易就得出以下代码(为了方便我们暂时只做到第10行): Sub delblankrow() For i% = 2 To 10
If Cells(i, 2) = "" Then Rows(i).Delete Next End Sub 这个代码应该是非常容易看明白的,也可将Cells(i, 2) = ""改成len(cells(I,2))<1。真假空都能删除。 可是,还是有点没对呢?当有连续空单元格时,运行一次是删除不完的,为什么会这样?探究一下原因(我们把B列内容都清除了,并在代码中增加一行): Sub delblankrow() For i% = 2 To 10
If Cells(i, 2) = "" Then Rows(i).select ‘增加这么一行,先选中这行后再删除。 Rows(i).Delete endif Next End Sub 增加了Rows(i).select这么一行,先选中这行后再删除,很清楚地看到:i=4时,判断Cells(4, 2)为空,选中了这行然后删除了,i增加1,成了5,就去判断Cells(5, 2)是否为空去了,这里产生了遗漏!因为Cells(4, 2)为空在删除第四行时,第五行自然上移成了第四行(后面的行都依次上移了一行),下一个判断Cells(5, 2)是否为空时这个Cells(5, 2)实际上是以前的第六行,以前的第五行(现在的第四行)就没有去判断了。这就是这段代码出错的原因所在! 明白了产生错误的原因,那么如何来改呢?先给出个简单的、间接的改法,倒循环: 前面的错误是因为删除一行后下面的行上移而造成漏判,因此我们可以改成从最后一行开始往上进行判断删除(为什么这样就没遗漏了呢?): Sub delblankrow2() For i% = 10 To 2 step -1
If Cells(i, 2) = "" Then Rows(i).select‘增加这么一行,先选中这行后再删除。实际代码中去除它 Rows(i).Delete endif Next End Sub 那么只能用倒循环吗?再来看看:第一段代码的错误是在于某行删除了后,下面的行依次上移,代码没有考虑到上移这一情况。我们现在将这一情况考虑进去:删除某行、下面的行自动依次上移后再对这行进行判断: Sub delblankrow3() r%=2 For i% = 2 To 10
If Cells(r, 2) = "" Then Rows(r).Delete
else r=r+1
endif Next End Sub 这样,多用一个变量来表示当前需要判断的行就达到目的了。对于以下代码,请大家自己来理解理解其思路: Sub delblankrow4() r% =10 i%=2 Do While i <=r
If Cells(i, 2) = "" Then
Rows(i).Delete
r=r-1
Else
i = i + 1
End If Loop End Sub 后面这两个虽然都可以达到目的,但都不如delblankrow2简洁明了,看来还是用delblankrow2这样的倒循环要好些。delblankrow2完整的代码应该如下: Sub delblankrow5() Application.ScreenUpdating = False '关闭屏幕刷新 For i% =10 To 2 Step -1
If Cells(i, 2) = "" Then Rows(i).Delete Next End Sub 说到这里,看似这个问题就结束了。 可是,这只做到了2至10行,要是改到如开头时讲的到25001行或者更多行,用这个代码那慢得要死!不信你试试。慢的原因是什么?慢就慢在两个方面:读单元格的次数太多(每次判断都要读一次单元格)、删除的次数太多(每有一个空就删除一次)。要想提速,必须从这两方面入手!对于读单元格的次数太多可以如以前所用的办法:定义一个数组,将需要判断的B列数据事先一次性读入数组,循环判断时只读取数组而不是去读单元格,从而达到提速的目的。而对于删除的次数太多的问题,就要用到点技巧了,我们可以这样来想:在一开始时回顾了录制宏,并得到了删除一行、连续的几行、不连续的几行的宏代码,如删不连续几行的Range("8:8,12:15,23:33").Delete,要是我们能将需要删除的行先找出来,然后按规则构造成形如"8:8,12:15,23:33"的字符串存入变量delstr中,然后再用Range(delstr).Delete,行不?要是可行的话,多行不是只删一次就可以了吗?马上试验,将delblankrow2按这个思路来改看行不: Sub delblankrow6() Dim arr1(), delstr$ arr1 = [b1:b10].Value'将B列数据读入数组 For i% = UBound(arr1) To LBound(arr1) + 1 Step -1'从数组的最大下标循环到最小下标+1,步长为-1
If arr1(i, 1) = "" Then delstr = delstr & "," & i & ":" & i'构造字串 Next If Len(delstr) > 1 Then'delstr有没有内容
delstr = Right(delstr, Len(delstr) - 1)'构造的字串delstr的第一个字符为,得去掉
Range(delstr).Delete'删除delstr所指定的行 End If End Sub 执行一下,是可行的。这样,删除多行只执行了一次delete,达到了提速的目的。 我们把它扩展到25001行,怎么错了呢?出现了错误提示: 为什么少的时候可行而多了就不行了呢?而多少恰巧是delstr的内容的多少的问题。为此我们可以将delstr分成几段来分次删除,以下实现这一思想: Sub delblankrow7() Dim arr1(), delstr$ Application.ScreenUpdating = False '关闭屏幕刷新 arr1 = [b1:b25001].Value'将B列数据读入数组 For i% = UBound(arr1) To LBound(arr1) + 1 Step -1'从数组的最大下标循环到最小下标+1,步长为-1
If arr1(i, 1) = "" Then
delstr = delstr & "," & i & ":" & i'构造字串
If Len(delstr) > 240 Then‘字串达到一定长度执行删除,经试验240是个合适的值
delstr = Right(delstr, Len(delstr) - 1)'构造的字串delstr的第一个字符为,得去掉
Range(delstr).Delete'删除delstr所指定的行
delstr = ""'将字串delstr清空
End If
End If Next If Len(delstr) > 1 Then'delstr还有没有内容
delstr = Right(delstr, Len(delstr) - 1)'构造的字串delstr的第一个字符为,得去掉
Range(delstr).Delete'删除delstr所指定的行 End If End Sub 至此,大部完成了,基本可以接受。 还有一点:连续空行!目前的代码对于如本例中的连续空行4、5、6、7其生成的字串是”4:4,5:5,6:6,7:7”对不?而我们前面的录制的宏Range("8:8,12:15,23:33").Delete中,这样的连续空行表示成”4:7”就成了,如何才能达到这个目的呢?需要我们增加个连续空行的,那就再来改嘛: Sub delblankrow8() Dim arr1(), delstr$ Application.ScreenUpdating = False '关闭屏幕刷新 arr1 = [b1:b25001].Value'将B列数据读入数组 For i% = UBound(arr1) To LBound(arr1) + 1 Step -1'从数组的最大下标循环到最小下标+1,步长为-1
If arr1(i, 1) = "" Then
tmpi = i - 1
Do While tmpi > 1
If arr1(tmpi, 1) <> "" Then
delstr = delstr & "," & i & ":" & tmpi + 1 '构造字串
i = tmpi
tmpi = 1
Else
tmpi = tmpi - 1
End If
Loop
If Len(delstr) > 240 Then
delstr = Right(delstr, Len(delstr) - 1) '构造的字串delstr的第一个字符为,得去掉
Range(delstr).Delete '删除delstr所指定的行
delstr = "" '将字串delstr清空
End If End If Next If Len(delstr) > 1 Then 'delstr有没有内容
delstr = Right(delstr, Len(delstr) - 1) '构造的字串delstr的第一个字符为,得去掉
Range(delstr).Delete '删除delstr所指定的行 End If End Sub 这样一来,相对来说较为满意了。 但同时这个代码给出了一个很不好的范例:for循环中对循环变量进行了更改! 可是还是有点不满意:示例中的空,有的是真空有的是假空,对于真空而言,e提供了个定位空值的功能,为什么我们不先定位真空并删除真空所在的行,再用上面的代码来删除假空所在的行呢?这里我们仅给出定位B列空值并删除其所在行的录制宏经修改后的代码: Columns("B:B").SpecialCells(xlCellTypeBlanks).EntireRow.Delete 要实现上述思想大家自行去组合吧。 -------------------------------------------------------------------------------------------------------------- 上面的代码全部都是按循环判断是否为空来决定是否删除这一思路来做的。其实我们还可以换换思路:在循环判断后,不删除他,而是在右边增加个辅助列,来记录是否为空,完成后利用E自带的排序,将为空的(不管真空还是假空)都排在一起,然后一次性删除就行了。为此我们将Sub delblankrow6()改成这一思想: Sub delblankrow9() Dim arr1() arr1 = [b1:b25001].Value '将B列数据读入数组 For i% = 2 To UBound(arr1) '从数组的最大下标循环到最小下标+1,步长为-1
If arr1(i, 1) <> "" Then
arr1(i, 1) = 1 '这是随意赋的值
Else '对于此例,else和arr1(i,1)=""都可以不要
arr1(i, 1) = ""
End If Next [d1].Resize(i - 1) = arr1'写入一个空列,为辅助列 [a2].Resize(i - 2, 4).Sort ([d1])'按辅助列排序,空值就都被排到后面去了 Range("25001:" & 1 + Cells(i, 4).End(xlUp).Row).Delete'删除后面的空值所在的行 Range("D:D").Clear'清空辅助列 End Sub 还有没有其它方法呢?如筛选等等?我想,肯定有,大家自己去试吧。 “思路决定出路”,确实不假,只要思路正确,把他翻译成VBA代码就成了,堆积木嘛,啷个堆都成,哈哈…… |