There are many ways to delete empty rows and columns in Exce, relatively simple to delete empty rows, just filter, filter out blank lines, delete them, but it is difficult to delete empty columns. Because you can't filter the deletion by column. Excel does not have this
A feature. Of course you can use a different method, that is, sort columns by column to sort the blank columns together, and then delete, but this method faces the problem of the order of the column is disturbed, thankless. This article will take advantage of Excel's powerful VBA features to complete the deletion
Methods except for blank rows and columns.
Step one, open Excel, press ALT+F11 combination build, bring up the VBA program window
Step two, in the Insert menu, select the module, insert a module
Step three, in the Code window in the new module, copy the following code in
' Delete blank line
Sub Deleteemptyrows ()
Dim LastRow as Long, R as Long
LastRow = ActiveSheet.UsedRange.Rows.Count
LastRow = LastRow + activesheet.usedrange.row-1
for r = LastRow to 1 Step-1
If Worksheetfunction.counta (Rows (r)) = 0 Then rows (r). Delete
Next R
End Sub
' Delete empty columns
Sub Deleteemptycolumns ()
Dim Lastcolumn as Long, c as long
Lastcolumn = ActiveSheet.UsedRange.Columns.Count
Lastcolumn = Lastcolumn + ActiveSheet.UsedRange.Column
For C = Lastcolumn to 1 Step-1
If Worksheetfunction.counta (Columns (c)) = 0 Then Columns (c). Delete
Next C
End Sub
When you are done, close the VBA window, select Save, and save the Excel file.
Step four, keep the Excel file in step three open, open the Excel file you want to work on, and execute the two macros above.
Delete blank rows and columns with VBA
Author: Unknown article source: Unknown Hits: 3063 update time: 2009-1-13 16:32:10
There are many ways to delete empty rows and columns in Excel by using VBA code to delete blank rows and columns in the specified range of worksheets:
Option Explicit
Sub delete_empty_rows ()
Dim rnarea As Range
Dim lnlast Row as long, I as Long, J as Long
application.screenupdating = False
lnlastro w = Selection.Rows.Count
Set rnarea = Selection
j = 0
 &N Bsp For i = Lnlastrow to 1 Step-1
If application.counta (rnarea.rows (i)) = 0 Then
; rnarea.rows (i). Delete
J = j + 1
end If
& nbsp; Next i
rnarea.resize (lnlastrow-j). Select
application.screenupdating = True
End Sub
Sub Delete_empty_columns ()
Dim lnlastcolumn As Long, I as Long, J as Long
Dim Rnarea as Range
&nb sp; 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
Rnarea.columns (i). Delete
J = j + 1
end If
& nbsp; Next i
rnarea.resize (, lnlastcolumn-j). Select
application.screenupdating = False
End Sub
Before you run the code, select the area where you want to delete empty rows and columns. If you want to delete a blank row, run Delete_empty_rows () and the empty row in the specified range is deleted. Note that the so-called empty line is in the specified area, if within the specified area is
Blank lines, but not blank lines outside the specified range, the blank lines will be deleted. Delete empty columns to run Delete_empty_columns ().