Hello,
I have code below, that currently targets and inserts a blank row above whatever I have set to Autofiltered.
In this case I have already manually set the Autofilter.
Instead of ABOVE what it Autofiltered, I was wondering if there is a way to insert a row BELOW what is Autofiltered.
Example with Filters Applied:
<tbody>
</tbody>
Example After Code Run: This example negates other data that might exist after Autofilter is turned off.
<tbody>
</tbody>
Desired Effect: Lines inserted below what is Autofiltered instead of above.
<tbody>
</tbody>
I have code below, that currently targets and inserts a blank row above whatever I have set to Autofiltered.
In this case I have already manually set the Autofilter.
Instead of ABOVE what it Autofiltered, I was wondering if there is a way to insert a row BELOW what is Autofiltered.
Code:
Sub InsRws()
' *This code assumes Autofilter is already applied
Dim Rng As Range
Set Rng = Intersect(Columns(1), ActiveSheet.UsedRange).SpecialCells(xlCellTypeVisible)
Range("A1").AutoFilter
Rng.EntireRow.Insert
Range("A1").Select
' *Delete Excess Header blank row created by code
Rows("1:1").Select
Selection.Delete Shift:=xlUp
' *Insert Blank Line in row 2 for empty selection
Rows("2:2").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub
Example with Filters Applied:
A (Filtered by Date) | B (Filtered by Type) | C |
Date | Fruit | Price |
1/15/2019 | Apple | .99 |
1/15/2019 | Banana | .50 |
1/15/2019 | Clementine | .25 |
<tbody>
</tbody>
Example After Code Run: This example negates other data that might exist after Autofilter is turned off.
A (Filtered by Date) | B (Filtered by Type) | C |
Date | Fruit | Price |
1/15/2019 | Apple | .99 |
1/15/2019 | Banana | .50 |
1/15/2019 | Clementine | .25 |
<tbody>
</tbody>
Desired Effect: Lines inserted below what is Autofiltered instead of above.
A (Filtered by Date) | B (Filtered by Type) | C |
Date | Fruit | Price |
1/15/2019 | Apple | .99 |
1/15/2019 | Banana | .50 |
1/15/2019 | Clementine | .25 |
<tbody>
</tbody>