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.
Table of Contents
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.
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.
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.
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 InsertMultipleRows()
Dim destRow As Range
Dim numRowsToInsert As Long
Dim i As Integer
' Prompt the user for the number of rows to insert
numRowsToInsert = InputBox("How many rows do you want to insert?", "Insert Rows")
' Validate the input
If IsNumeric(numRowsToInsert) And CInt(numRowsToInsert) > 0 Then
numRowsToInsert = CInt(numRowsToInsert)
' Prompt the user to select a cell where the rows will be inserted
Set destRow = Application.InputBox("Click on the cell where you want to insert rows", "Select Destination", Type:=8)
' Check if a cell was selected
If Not destRow Is Nothing Then
' Turn off screen updating for faster processing
Application.ScreenUpdating = False
' Insert the specified number of rows below the selected cell
For i = 1 To numRowsToInsert
Rows(destRow.Row + 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Next i
' Turn screen updating back on
Application.ScreenUpdating = True
Else
MsgBox "No cell was selected. Please run the macro again."
End If
Else
MsgBox "Invalid input. Please enter a positive number."
End If
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.
5. Video: Insert Multiple Rows or Columns
This Excel video tutorial , we’ll explore four different methods that will help you manage your data with ease, even when working with large datasets.
Leave a Reply
You must be logged in to post a comment.