[no0000c1] Excel removes blank lines and blank columns in VBA code

There are many ways to delete empty rows and columns in exce, it is relatively simple to delete empty rows, just filter, filter out blank lines, Delete them, But it is difficult to delete empty Columns. Because you cannot filter by column to Delete. This feature is not in Excel. 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 take advantage of the powerful VBA features in Excel.

Suppose you want to delete a blank row or a blank column as In:

file-"options-" Custom Ribbon-main tab-"development Tools

Insert button

Either a form control or an ActiveX control Can.

Introduce the form controls First:

ALT+F11 Open the VB macro editor, Insert the module, and enter the following code:

'Delete empty linesSubdeleteemptyrows ()DimLastRow as LongR as LongLastRow=ActiveSheet.UsedRange.Rows.CountLastRow= LastRow + ActiveSheet.UsedRange.Row-1 forr = LastRow to 1 Step-1    IfWorksheetfunction.counta (Rows (r)) =0  thenRows (r). DeleteNextREnd Sub'Delete Empty columnsSubdeleteemptycolumns ()DimLastcolumn as LongC as LongLastcolumn=ActiveSheet.UsedRange.Columns.CountLastColumn= Lastcolumn +ActiveSheet.UsedRange.Column forc = Lastcolumn to 1 Step-1    IfWorksheetfunction.counta (Columns (c)) =0  thenColumns (c). DeleteNextCEnd Sub

After you close the editor, drag a button in the interface, and in the popup window, select the deleteemptycolumns(that is, The delete empty column you just inserted) function method.

Right-click the button you just dragged, right-click Edit text to delete empty column

Then in non-"design mode", click Test

similarly, Add the Delete empty row button

When you save, a warning pops up:

therefore, you need to save as a macro-enabled Workbook.

and then Save.

Then take the ActiveX control as an example:

After dragging the button, open design mode

In design mode, Right-click to modify the name of the button, which is the Caption property

Double-click to open a message box

In the editor that opens, enter

    MsgBox (" message box ")

Close design mode and editor, click button Test

The same can be done to create two ActiveX buttons, complete deleting the blank columns of the selection and deleting the blank rows in the selection

The following approach is to use VBA code to delete blank rows and empty columns in the specified range of the Worksheet:
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 blank line is in the specified range, and if it is a blank row within the specified range and not a blank row outside the specified range, the empty rows will still be deleted. Deleting an empty column runs Delete_empty_columns ().

however, The code now works on Sheet1.

Private SubCommandButton1_Click ()MsgBox("message Box")End SubPrivate SubCommandButton2_Click () delete_empty_rowsEnd SubPrivate SubCommandbutton3_click () delete_empty_columnsEnd Sub'Option Explicit'Delete the blank row in the selectionSubdelete_empty_rows ()DimRnarea asRangeDimLnlastrow as Long, I as LongJ as Longapplication.screenupdating=FalseLnlastrow=Selection.Rows.CountSetRnarea =SelectionIfRnArea.Rows.Count <=1  then        MsgBox("Please select a section with more than one line containing text first")        GoTolastlineEnd IfJ=0      fori = Lnlastrow to 1 Step-1        IfApplication.counta (rnarea.rows (I)) =0  thenrnarea.rows (i). Delete J= j +1        End If    NextIIf(lnlastrow-j) >0  thenrnarea.resize (lnlastrow-j). SelectEnd Ifapplication.screenupdating=Truelastline:End Sub 'Delete the blank column of a selectionSubdelete_empty_columns ()DimLnlastcolumn as Long, I as LongJ as Long    DimRnarea asRange application.screenupdating=FalseLnlastcolumn=Selection.Columns.CountSetRnarea =SelectionIfRnArea.Columns.Count <=1  then            MsgBox("Please select a section with multiple columns containing text first")            GoTolastlineEnd IfJ=0      fori = Lnlastcolumn to 1 Step-1        IfApplication.counta (rnarea.columns (I)) =0  thenRnarea.columns (i). Delete J= j +1        End If    NextIIf(lnlastcolumn-j) >0  thenrnarea.resize (, Lnlastcolumn-j). SelectEnd Ifapplication.screenupdating=Falselastline:End Sub

Adjust the position of the button can be Used. however, Sometimes the button will block the text, so you can move it to the quick access toolbar.

Customize to toolbars for quick access

Excel options-customize-select a command from the following Location-select Macro-step to add to the custom Quick Access Toolbar

Accessories: Http://files.cnblogs.com/files/Chary/DDD.rar

[no0000c1] Excel removes blank lines and blank columns in VBA code