Remove blank rows and columns in Excel with VBA implementation

The use of VBA in Excel to remove empty rows and empty columns in the EXCE to delete empty rows and empty columns in a number of ways, relatively simple to delete empty lines, just filter, blank lines to filter out, delete, but it is difficult to delete empty columns. Because you cannot filter by column to delete. Excel does not have this

Functions. Of course you can use another method, that is, sort by column, sort the blank columns together, and then delete, but this method faces the problem that the order of the columns is disturbed, thankless. This article will use the powerful VBA feature in Excel to complete the deletion

Methods in addition to blank rows and columns.

Step one, open Excel, press ALT+F11 to build, bring up the VBA program window

Step two, in the Insert menu, select Module, insert a module

Step three, in the code window of the new module, copy the following code in

' Delete empty lines

Sub deleteemptyrows () Dim lastrow as Long, r as Long lastrow = ActiveSheet.UsedRange.Rows.Count lastrow = lastrow + Active Sheet.usedrange.row-1

for r = lastrow to 1 Step-1 If Worksheetfunction.counta (Rows (r)) = 0 Then Rows (r). Delete Next R End Sub ' remove empty column

Sub deleteemptycolumns () Dim Lastcolumn as Long, c as long lastcolumn = ActiveSheet.UsedRange.Columns.Count Lastcolumn = L Astcolumn + 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 finished, close the VBA window, select Save, and save the Excel file.

Step four, keep the Excel file in step three open, open the need to work with the Excel file, execute the above two macros.

Use VBA to remove empty rows and empty columns unknown article source: Unknown Hits: 3063 Update: 2009-1-13 16:32:10
There are many ways to remove empty rows and columns in Excel, and the following method is to use VBA code to delete blank rows and empty columns in the specified range of the worksheet:

Option Explicit   Sub delete_empty_rows ()    Dim rnarea As Range    Dim lnlastrow As Long, I as L Ong, J as Long      application.screenupdating = False    Lnlastrow = Selection.Rows.Count     Set Rnarea = Selection      j = 0      for i = Lnlastrow to 1 Step-1   &nb sp;   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 = True End Sub   Sub Delete_empty_columns ()    Dim Ln Lastcolumn 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 App Lication. CountA (Rnarea.columns (i)) = 0 Then          rnarea.columns (i). Delete          J = j + 1       End If    Next I      rnarea.resize (, lnlastcolumn-j). Select      application.screenupdating = False End Sub

Before running 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 it is within the specified area

Blank rows, but not blank lines outside the specified range, these empty rows will still be deleted. Deleting an empty column runs Delete_empty_columns ().

2.

Sub Deleteempty ()

Call Deleteemptyrow

Call DELETEEMPTYCOLMN

End Sub

Sub Deleteemptyrow ()

' Delete blank rows with no values after column 2nd to line 95th between columns C

Dim I as Integer

For i = 94 to Step-1

If Cells (i, 3) = "Then

Sheets ("Sheet1"). Rows (i). Delete

End If

Next I End Sub

' Delete empty columns with no values for line 6th

Sub DELETEEMPTYCOLMN ()

Dim I as Integer

For i = 4 Step-1

If Cells (6, i) = "Then

Sheets ("Sheet1"). Columns (i). Delete

End If

Next I

End Sub

Thanks for sharing. In fact, using the location to delete faster, such as the deletion of rows 2nd to 95th between the column C no value of the empty row

Range ("c2:c94"). SpecialCells (4). Entirerow.delete

Remove blank rows and columns in Excel with VBA implementation