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