Forum Discussion
Solved
Inserting multiple rows
I know this is a very basic question but I can't find the answer in the documentation. I often want to insert a bunch of rows at a time. Currently I just do it one at a time but I would like to do so...
leisaellemor To automate this with VBA, open the Visual Basic editor (Alt+F11). In the left-hand pane under VBAProject > Microsoft Excel Objects, double-click the sheet name that contains your data to open the worksheet module. The code will vary slightly, depending on if your data has been formatted as an Excel Table, or if it's just a regular worksheet range.
Version 1: With a Structured Excel Table
Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) 'Stop macro if more than once cell was changed If Target.Cells.Count > 1 Then Exit Sub 'Apply filter if the criteria cell was changed Dim rg As Range Set rg = Me.Range("C1") If Not Intersect(Target, rg) Is Nothing Then Me.ListObjects(1).Range.AutoFilter Field:=3, criteria1:="<=" & rg.Value End If End SubVersion 2: With a Worksheet Range
Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) 'Stop macro if more than once cell was changed If Target.Cells.Count > 1 Then Exit Sub 'Apply filter if the criteria cell was changed Dim rg As Range Set rg = Me.Range("C1") If Not Intersect(Target, rg) Is Nothing Then Dim rgData As Range Set rgData = Me.Range("A2").CurrentRegion Set rgData = rgData.Offset(1).Resize(rgData.Rows.Count - 1) rgData.AutoFilter Field:=3, Criteria1:="<=" & rg.Value End If End SubNote: since the screenshot of your data does not reveal the worksheet column letters or rows numbers, I made the assumption that the table begins in cell A2, the column to filter is Field:=3 and the criteria to filter by is in cell C1. Please adjust the range references and field number, if necessary.
Also, Range("A2").CurrentRegion was used here to identify the entire data range. This method will only work properly if your data range does not contain any completely blank rows or columns... the CurrentRegion method identifies all adjacent data in the same manner that pressing Ctrl+Shift+8 (*) will highlight all adjacent data to the active cell. If your data contains additional adjacent cells above the header row, you may need to adjust the Offset and Resize numbers accordingly.
Then, just input a new value in the criteria cell to refresh the filter.
I didn't get it. The method mentioned above just inserts one row below each of the rows I have selected respectively. I'm wondering how to insert multiple rows only below exactly the row I have selected.mdebellis
Here is how I do it, in a bit more detail. (There are probably other ways to do it)
1) Select the row that you want to add rows above or below
2) Use control left click to select the number of rows you want to add. For example, suppose you have a spreadsheet with 10 rows and you want to add 5 more rows after row 5. Select row 5 then select rows 6-10.
3) Right click (making sure rows 5-10 are still selected) and then select Insert. It should insert 5 new rows below row 5.
1) Select the row that you want to add rows above or below
2) Use control left click to select the number of rows you want to add. For example, suppose you have a spreadsheet with 10 rows and you want to add 5 more rows after row 5. Select row 5 then select rows 6-10.
3) Right click (making sure rows 5-10 are still selected) and then select Insert. It should insert 5 new rows below row 5.
- I'm using Microsoft Office Professional Plus 2019. I think it might behave different from your excel due to version difference.