Category: Excel Basics

This post will guide you how to quickly insert multiple rows or columns in your Microsoft worksheet. You will see that how to add new multiple rows or columns with shortcut or insert feature, or insert copied cells in excel. Or how to quickly insert rows or columns with Excel VBA macro.

Suppose that you want to quickly insert 5 blank rows between row 4 and row5 in your worksheet, and how to do it? May be you can do it by manually, but this is not a good idea, because if you need to insert hundreds of blank rows or columns, it will be tedious and time-consuming.

Of course, there are several ways to quickly insert new multiple rows or column in Excel. Let’s see the below detailed description.

Method 1: Insert multiple rows or columns in Excel using Insert feature

Insert Rows:

If you want to insert 5 rows between Row 3 and Row 4 in your worksheet, then you can do it following steps:

1# you should locate the destination row, it should be Row 4, so select 5 entire rows below from Row 3 using the row number buttons.

2# On the HOME tab, click Insert command under Cells group. Or click Insert Cells from the drop-down list of Insert.

Or you can right-click on the selected rows, then choose the insert option from the pop-up menu list.

You will see that 5 rows are inserted after Row 3 in your worksheet.

Insert Columns:

If you want to insert 5 columns between column B and column C using insert feature, just do it follow:

1# select 5 columns next to column C (includes the column 3) using the column number buttons.

2# On the HOME tab, click Insert command under Cells group. Or click Insert Cells from the drop-down list of Insert.

Method 2: Insert multiple rows or columns in Excel using shortcut

You can also use the shortcut F4 to insert multiple rows or columns in Excel, and this is the simplest way to insert new rows or columns in Excel.

Insert Rows:

If you want to insert 5 rows between Row 2 and Row 3, then you just need to select the Row 3, then press F4 shortcut 5 times, then it will insert 5 rows after Row 2.

Insert Columns:

If you want to insert 5 columns between Column B and Column C, then you just need to select the entire Column C, then press F4 shortcut 5 times. You will see that 5 new columns are inserted in your worksheet.

Method 3: Insert multiple rows or columns in Excel using Insert Copied Cells feature

You can also use the Insert Copied Cells functionality to inert multiple rows or columns, just follow the below steps:

1# select multiple rows in your worksheet (the selected rows should be equal to the number of rows that you want to inert)

2# press Ctrl +C to copy the selected rows

3# select the destination rows, such as, if you want to insert new rows between Row2 and Row 3, then you should select Row 3. Then right-click on it, choose Insert Copied Cells option from the drop-down menu list.

4# you will see that multiple rows are inserted after row 2.

And if you need to insert multiple columns using Insert Copied Cells method, just select multiple columns in the above Step1. Then repeat the rest steps.

Method 4: Insert multiple rows in Excel using Excel VBA macro

If you want to insert multiple rows with VBA macro in Excel, you can follow the below steps:

1# click on “Visual Basic” command under DEVELOPER Tab.

2# then the “Visual Basic Editor” window will appear.

3# click “Insert” ->”Module” to create a new module

4# paste the below VBA code into the code window. Then clicking “Save” button.

Sub InsertRowsAtCursor()
    Answer = InputBox("How many Rows to insert? (50 Rows maximum)")
    NumLines = Int(Val(Answer))
    If NumLines > 50 Then
        NumLines = 50
    End If
    If NumLines = 0 Then
        GoTo EndInsertLines
    End If
    Do
        Selection.EntireRow.Insert
        Count = Count + 1
    Loop While Count < NumLines
    EndInsertLines:
End Sub

5# back to the current worksheet, then run the above excel macro. Click Run button.

6# type rows number that you want to insert, then click OK button.

This post will guide you how to split a large workbook to separate files and each worksheet should be saved as a single file. How do I split the whole workbook into several excel worksheets or single excel file.

Assuming that you need to split your workbook that contains lots of worksheets into multiple single Excel file and its extension name is CSV.

The simplest way is to copy each worksheet to a single excel workbook or file by manually. So you can select one worksheet that you want to save as a single excel file, then create a new workbook, paste it in the new workbook. Then save it.

If you have hundreds of worksheet in your workbook, then it will be time-consuming and tedious. So do we have a quick way to split workbook to separate excel file? Of course yes, the below will describe one way to split your workbook with VBA code.

Split Excel workbook into separate files with VBA code

Let’s see the following steps:

If you want to quickly split a large workbook into several single excel file for each worksheet, then you can write a new Excel VBA macro code to achieve the result. And the below VBA code will save the separated excel file at the directory same as with the master workbook.

1# click on “Visual Basic” command under DEVELOPER Tab.

2# then the “Visual Basic Editor” window will appear.

3# click “Insert” ->”Module” to create a new module

4# paste the below VBA code into the code window. Then clicking “Save” button.

Sub SplitWorkbook()
    Dim workbookPath As String
    workbookPath = Application.ActiveWorkbook.Path
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    For Each wSheet In ThisWorkbook.Sheets
        wSheet.Copy
        Application.ActiveWorkbook.SaveAs Filename:=workbookPath & "\" & wSheet.Name & ".xlsx"
        Application.ActiveWorkbook.Close False
    Next
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

5# back to the current worksheet, then run the above excel macro. Click Run command.

This post will teach you how to use the Excel VBA macro code to copy a Cell to clipboard with only single click in Excel. How do I automatically copy the content of cell to Clipboard while clicking on the Cell or just single click on the cell in Microsoft Excel 2016, 2013 and older versions.

Normally, to copy a cell to the clipboard, you need to select a Cell firstly, press Ctrl + C shortcut to copy the content into clipboard. Whether there is any other ways to omit one or two steps to copy a cell to clipboard. Of course yes, you can try to write an Excel Macro code to copy cell content into clipboard while you click on that cell.

Copy a Cell to Clipboard with Single Click with VBA Macro

Let’s see the below detailed steps:

1# open your worksheet contain cells that you want to copy them to clipboard.

2# right click on the sheet tab, then select View Code menu from the pop-up menu list.

3# then the “Visual Basic Editor” window will appear

4# paste the below VBA code into the code window. Then clicking “Save” button.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("A1:A4")) Is Nothing Then
        Target.Copy
    End If
End Sub

5# try to click cell A1 to A4, you will see that all Cell that you clicked are copied to clipboard automatically.

If you want to use this VBA macro code, just need to update the Range as you need.

This tutorial will teach you how to change positive numbers to negative with Paste special multiply operation in excel. How to convert positive number to negative or how to reverse the number signs with VBA code in Excel.

Convert Positive Numbers to Negative with Paste Special Operation

To change positive numbers to negative in excel, you can do it with copy and paste, you need to copy a cell that contains a negative number -1, then select the cells that contain the positive numbers, and use the multiply operation on the Pasted Special menu. Then it will perform the multiplication on the selected cell. So all positive number will be change to negative.

You can do it as following steps:

1# type one negative number –1 in one cell

2# select that cell that contains -1 and then copy it.

3# select the range of cells that contain positive numbers, then right-click on it, select Paste Special from the pop-up menu list.

4# the Paste Special window will appear.

5# select Multiply radio button from the Operation section.

6# Click OK button, you will see that all of the positive numbers should be change to negative.

If the selected range of cells contains negative numbers, then its values also will be multiplied by -1, so its signs also will be reversed as negative.

You can also write an Excel formula to multiply one negative number -1 to achieve the same result. the generic formula is like this:

=B1* -1

Convert Positive Numbers to Negative with VBA Code

You can also write a new excel macro to convert positive numbers to negative numbers in Excel VBA, just do the following:

1# click on “Visual Basic” command under DEVELOPER Tab.

2# then the “Visual Basic Editor” window will appear.

3# click “Insert” ->”Module” to create a new module

4# paste the below VBA code into the code window. Then clicking “Save” button.

Sub ChangePositiveToNegative()
    Dim myCell As Range 
    Set W = Application.Selection
    Set W = Application.InputBox("Select one range that you want to change positive to negative:", "ChangePositiveToNegative", W.Address, Type:=8)
    Set W = W.SpecialCells(xlCellTypeConstants, xlNumbers)
    For Each myCell In W
        xValue = myCell.Value
        If xValue > 0 Then
            myCell.Value = xValue * -1
        End If
    Next
End Sub

5# back to the current worksheet, then run the above excel macro.

This post will teach you how to quickly insert or add multiple checkboxes in excel. How to add one checkboxes in one cell in Excel. How to add multiple checkboxes with Fill Handle or with VBA Macro in Excel.

Add a checkbox in one cell

If you want to add one checkbox in a cell, you can do it as following steps:

1# go to Developer Tab, then click Insert command under Controls group, select Check Box.

2# click one cell that you want to insert the checkbox. And it will appear in that cell.

3# you can move the cursor to drag the checkbox to the desired position.

Insert multiple checkboxes with Fill Handle

If you want to quickly add multiple checkboxes in Excel, you can use the Fill handle to create multiple checkboxes based on one checkbox that has been created. Refer to the following steps:

1# insert one checkbox as described above

2# move the checkbox into a cell, then select that cell, Drag the Auto Fill Handle over the range of cells that you want to fill.

3# you will see that multiple checkboxes have been added in each cell.

Insert multiple checkboxes with VBA code

You can also write a new excel macro to quickly add multiple checkboxes in Excel VBA, just do the following:

1# click on “Visual Basic” command under DEVELOPER Tab.

2# then the “Visual Basic Editor” window will appear.

3# click “Insert” ->”Module” to create a new module

4# paste the below VBA code into the code window. Then clicking “Save” button.

Sub addMultipleCheckBoxes()

  On Error Resume Next
    Set wr = Application.Selection
    Set wr = Application.InputBox("Select one Range that you want to add checkbox in:", "addMultipleCheckBoxes", wr.Address, Type:=8)
    Set wsheet = Application.ActiveSheet
    
    i = 1
    For Each R In wr
        With wsheet.CheckBoxes.Add(R.Left, R.Top, R.Width, R.Height)
            With Selection
               .Characters.Text = R.Value

               .Caption = "Check Box" & i
               i = i + 1
           End With
        End With
    Next
    wr.ClearContents
    wr.Select
End Sub

5# back to the current worksheet, then run the above excel macro.

Change the checkbox name and caption name

When you create a new checkbox, the default caption name is like as CheckBox x. so if you want to change the caption name, you just need to right click the checkbox, select the Edit Text from the drop-down menu, and then type the name that you want to set.

If you want to change the checkbox name, you just need to select the checkbox, then type the desired name in the Name box, it will change immediately.

This post will guide you how to delete blank or empty rows or rows that contain blank cells in excel. If you want to remove blank rows that contain blank cells, you can use the Go To Special command. But if you just want to remove rows that are entirely blank, you should use the Filter blanks cell way.

Delete rows that contain blank cells

You can use the Go To Special command to remove all rows that contain blank cells, just do the following:

1# on the HOME tab, click Find & Select command under Editing group

2# click Go To Special

3# Select Blanks radio button and then click OK button.

4# you will see that all rows that contain blank cells are selected.

5# go to HOME tab, click Delete command under Cells group. Then click Delete Sheet Rows from the drop down menu list.

Delete Blank Rows

If you want to remove blank rows in excel, and you need to find the entire blank rows firstly, so you can use the Filter function to filter all blank rows, just do this following:

1# select the range of cells that you want to remove blank rows

2# on the DATA tab, click Filter command under Sort& Filter group.

3# click Filter arrow button and uncheck the Select All box, just check only Blanks, then all blank rows in that column will be filtered.

4# you need to continue to filter other columns for blank cells until all cells in the selected range are blank.

5# select all the filtered rows, go to HOME tab, click Delete command under Cells group. Then click Delete Sheet Rows from the drop down menu list.

Or just right-click on the selected cell and then click Delete Row from the drop-down menu list.

6# on the DATA tab, click Clear command under Sort & Filter group.

7# you will see that all blank rows are removed.

Delete blank rows in Excel VBA

You can also write an excel Macro to delete blank rows in excel, just refer to the following steps:

1# click on “Visual Basic” command under DEVELOPER Tab.

2# then the “Visual Basic Editor” window will appear.

3# click “Insert” ->”Module” to create a new module

4# paste the below VBA code into the code window. Then clicking “Save” button.

Sub RemoveBlankRows()
    Set myRange = Application.Selection
    Set myRange = Application.InputBox("Select one Range that you want to remove blank rows", "RemoveBlankRows", myRange.Address, Type:=8)
    xRows = myRange.Rows.Count
    For i = xRows To 1 Step -1
        If Application.WorksheetFunction.CountA(myRange.Rows(i)) = 0 Then
            myRange.Rows(i).EntireRow.Delete XlDeleteShiftDirection.xlShiftUp
        End If
    Next
End Sub

5# back to the current worksheet, then run the above excel macro.