VBA常用代码解析

031删除工作表中的空行

如果需要删除工作表中所有的空行，可以使用下面的代码。

Sub DelBlankRow()

DimrRow As Long

DimLRow As Long

Dimi As Long

rRow= Sheet1.UsedRange.Row

LRow= rRow + Sheet1.UsedRange.Rows.Count - 1

Fori = LRow To rRow Step -1

If Application.WorksheetFunction.CountA(Rows(i))= 0 Then

Rows(i).Delete

End If

Next

End Sub

代码解析：

DelBlankRow过程删除工作表中已使用的区域的所有空行。

第5行代码获得工作表中已使用区域的首行行号，其中使用UsedRange属性返回工作表中已使用的区域。

第6行代码获得工作表中已使用区域的最后一行行号。

第7行到第11行代码从最大行数至最小行数循环判断指定行是否为空行，若为空行则删除该行。

注意此处一定要从最大行数至最小行数开始循环判断，因为如果工作表中存在两行及两行以上的相邻空行，从最小行数开始循环删除的话，当第一行空行被删除后，被删除行下面的一行会往上移位，而此时For...Next循环的计数器已经加1，所以会出现漏删除的现象。

其中第8、9行代码使用工作表CountA函数判断当前行已使用单元格的数量，如果为零说明此行是空行则使用Delete删除。

应用于Range对象的Delete方法删除对象，语法如下：

expression.Delete(Shift)

参数expression是必需的，返回一个Range对象。

参数Shift是可选的，指定删除单元格时替补单元格的移位方式。可为以下 XlDeleteShiftDirection常量之一：xlShiftToLeft或xlShiftUp。如果省略该参数，则Microsoft Excel将根据区域的图形决定移位方式。

▲032删除工作表的重复行

在实际应用中，可能需要删除工作表中A列的重复内容而只保留一行，那么可以借助工作表CountIf函数来完成，如下面的代码所示。

Sub DeleteRow()

DimR As Integer

Dimi As Integer

WithSheet1

R = .[a65536].End(xlUp).Row

For i = R To 1 Step -1

If WorksheetFunction.CountIf(.Columns(1)，.Cells(i，1)) > 1 Then

.Rows(i).Delete

End If

Next

EndWith

End Sub

代码解析：

DeleteRow过程删除工作表A列重复单元格所在的整行内容，只保留一行。

第5行代码取得工作表中A列的最后一个非空单元格的行号，关于Range对象的End属性请参阅▲3 。

第6行到第10行代码从最大行数至最小行数循环判断A列单元格内容是否重复并删除重复单元格所在的整行。和▲32 一样，此处For...Next循环也要从最大行数至最小行数开始循环判断，否则可能会删除不净。其中第7行代码使用工作表CountIf函数判断单元格内容是否重复，如果重复则删除该单元格所在的行。

▲033定位删除特定内容所在的行

如果需要删除工作表区域中特定内容所在的行，可以使用定位的方法快速删除，无需使用For...Next循环对单元格逐个进行判断。

示例代码如下：

Sub SpecialDelete()

DimR As Integer

WithSheet1

R = .Range("a65536").End(xlUp).Row

.Range("a2:a" & R).Replace"Excel"，““，2

.Columns(1).SpecialCells(4).EntireRow.Delete

EndWith

End Sub

代码解析：

SpecialDelete过程删除工作表A列单元格中显示为“Excel”的行。

第5行代码使用Replace方法将工作表A列中显示为“Excel”的单元格内容替换成空白。关于Replace方法请参阅▲6 。

第6行代码使用SpecialCells方法定位到工作表A列中所有的空单元格，使用Range对象的EntireRow属性返回其所在的整个行一次性删除。关于SpecialCells方法请参阅▲4 。

▲034判断是否选中整行

通过当前选择的单元格区域的单元格数目与行数或列数相比较，判断用户是否选中了整行或整列，如下面的代码所示。

Private Sub Worksheet_SelectionChange(ByVal TargetAs Range)

If Target.Rows.Count= 1 Then

If Target.Columns.Count = 256 Then

MsgBox "您选中了整行，当前行号" & Target.Row

End If

EndIf

End Sub

代码解析：

工作表的SelectionChange事件，判断用户是否选中了工作表中一整行单元格区域。

第2行代码中的Target.Rows.Count返回目标区域的行数，确定用户当前选择区域的总行数是否为1。

第3行代码中的Target.Columns.Count返回目标区域的列数，确定用户当前选择区域总列数是否为256。

第4行代码当用户选中一整行时显示一个消息框，提示用户当前选择的行号。

▲035限制工作表的滚动区域

如果希望限制工作表中滚动的区域，可以通过设置WorkSheet对象的ScrollArea属性来实现。ScrollArea属性使用以A1样式的区域引用形式(字符串类型)返回或设置工作表允许滚动的区域。当设置了工作表滚动区域之后，用户不能选定滚动区域之外的单元格，但仍然可以选定区域之外的其他对象(例如图形、按钮等)，同时工作表的一些相应功能可能被禁止(例如工作表全选、选中整行或整列等)。

在VBE中的工程管理窗口选择相应工作表对象，然后在其属性窗口中设置ScrollArea属性，即可限制工作表中滚动的区域。

但是Excel不会记忆该项设置，当再次打开该工作簿时，ScrollArea属性将被重置，用户必须重新设置ScrollArea属性才能限制工作表中的滚动区域，解决方法是使用代码在工作簿打开时对ScrollArea属性进行设置，如下面的代码所示。

Private Sub Workbook_Open()

Sheet1.ScrollArea= "B4:H12"

End Sub

代码解析：

工作簿的Open事件，在打开该工作簿时设置Sheet1工作表的滚动区域为“B4:H12”单元格区域。

如果需要取消滚动区域的限制，可以将ScrollArea属性值设置为空，如下面的代码所示。

Sheet1.ScrollArea =““

▲036复制自动筛选后的数据区域

用户在对数据列表进行自动筛选后，往往希望将自动筛选的结果复制到其它地方。

这时可以通过获取该列表区域中可见单元格的方法得到筛选结果的单元格区域，并复制到工作表Sheet2中，如下面的代码所示。

Sub CopyFilter()

Sheet2.Cells.Clear

WithSheet1

If .FilterMode Then

.AutoFilter.Range.SpecialCells(12).CopySheet2.Cells(1，1)

End If

EndWith

End Sub

代码解析：

CopyFilter过程将Sheet1表中的筛选结果复制到工作表Sheet2中。

第2行代码清除Sheet2表中数据。

第4行代码判断Sheet1表是否处于自动筛选状态。FilterMode属性返回工作表是否处于筛选模式，如果指定工作表中包含已筛选序列且该序列中含有隐藏行，则该值为True。

第5行代码通过AutoFilter对象的Range属性返回工作表的自动筛选列表区域，再使用SpecialCells方法获取该列表区域中可见单元格(SpecialCells方法请参阅▲4 )，得到筛选结果的单元格区域，然后使用Copy方法将结果区域复制到工作表Sheet2中，应用于Range对象的Copy方法将单元格区域复制到指定的区域或剪贴板中，语法如下：

expression.Copy(Destination)

参数expression是必需的，该表达式返回一个Range对象。

参数Destination是可选的，指定区域要复制到的目标区域。如果省略该参数，则将该区域复制到剪贴板中。

▲037使用高级筛选获得不重复记录

在数据列表中，如果要将其中不重复的记录复制到另一工作表中，则可以通过高级筛选功能实现。

示例代码如下：

Sub Filter()

Sheet1.Range("A1").CurrentRegion.AdvancedFilter_

Action:=xlFilterCopy，Unique:=True，_

CopyToRange:=Sheet2.Range("A1")

End Sub

代码解析：

Filter过程使用AdvancedFilter方法对单元格A1的当前区域筛选不重复的记录，并将筛选结果复制到工作表Sheet2中。应用于Range集合的AdvancedFilter方法语法如下：

AdvancedFilter(Action，CriteriaRange，CopyToRange，Unique)

参数Action是必需的，可以为表格所列的 XlFilterAction常量之一。

参数CriteriaRange指定高级筛选操作的条件区域，缺省时表示没有条件限制。

参数CopyToRange表示指定被复制行的目标区域，仅当Action为xlFilterCopy时有效，否则忽略本参数。

参数Unique指示是否选择不重复的记录，如果其值为True，则重复出现的记录仅保留一条；如果其值为 False(默认值)，则筛选出所有符合条件的记录。