excel删除空行空列.docxVIP

Excel中用VBA实现删除空行和空列?? 2012-04-10 17:13:57|??分类：? HYPERLINK /blog/ \l m=0t=1c=fks_087065084081083075087094074068083095087066086083 \o VB和VBA知识 VB和VBA知识|举报|字号?订阅 Excel中用VBA实现删除空行和空列在exce中删除空行和空列的方法有很多，相对而言删除空行较为简单，只需进行筛选，将空白行筛选出来，删除即可，但要删除空列比较困难。因为你不能按列进行筛选删除。Excel中没有这 个功能。当然你可以用另外一种方法，就是按列进行排序将空白列排序在一起，然后删除，但这种方法面临着列的顺序被打乱的问题，吃力不讨好。本文将利用Excel中强大VBA功能来完成删 除空白行和列的方法。 步骤一、打开excel，按ALT+F11组合建，调出VBA程序窗口 步骤二、在插入菜单中，选择模块，插入一个模块 步骤三、在新建模块中的代码窗口将以下代码复制进去 ‘删除空行 Sub DeleteEmptyRows()Dim LastRow As Long, r As LongLastRow = ActiveSheet.UsedRange.Rows.CountLastRow = LastRow + ActiveSheet.UsedRange.Row -1 For r = LastRow To 1 Step -1If WorksheetFunction.CountA(Rows(r)) = 0 Then Rows(r).DeleteNext rEnd Sub’删除空列 Sub DeleteEmptyColumns()Dim LastColumn As Long, c As LongLastColumn = ActiveSheet.UsedRange.Columns.CountLastColumn = LastColumn + ActiveSheet.UsedRange.ColumnFor c = LastColumn To 1 Step -1If WorksheetFunction.CountA(Columns(c)) = 0 Then Columns(c).DeleteNext cEnd Sub 完成后关闭VBA窗口，选择保存，并保存Excel文件。 步骤四、保持步骤三中的excel文件处于打开状态，打开需要处理excel文件，执行上述两个宏即可。 用VBA删除空行和空列??作者：未知 文章来源：未知 点击数：3063 更新时间：2009-1-13 16:32:10???????????? 在Excel中删除空行和空列的方法有很多，下面的方法是用VBA代码来删除工作表指定区域中的空行和空列： Option Explicit?Sub Delete_Empty_Rows()?? Dim rnArea As Range?? Dim lnLastRow As Long, i As Long, j As Long??? Application.ScreenUpdating = False?? lnLastRow = Selection.Rows.Count?? Set rnArea = Selection??? j = 0??? For i = lnLastRow To 1 Step -1????? If Application.CountA(rnArea.Rows(i)) = 0 Then???????? rnArea.Rows(i).Delete???????? j = j + 1????? End If?? Next i??? rnArea.Resize(lnLastRow - j).Select??? Application.ScreenUpdating = TrueEnd Sub?Sub Delete_Empty_Columns()?? Dim lnLastColumn As Long, i As Long, j As Long?? Dim rnArea As Range??? Application.ScreenUpdating = False?? lnLastColumn = Selection.Columns.Count?? Set rnArea = Selection??? j = 0??? For i = lnLastColumn To 1 Step -1????? If Application.CountA(rnArea.Columns(i)) = 0 Then????????