Delete blank rows and columns with VBA implementation in Excel

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 ().