Solved: Insert Rows, AutoFilter, xlCellTypeVisible

[Philcjr]

I need some help figuring out what I am doind wrong here.

The goal:
- Perform Autofilter
- Then, with the rows that are left visible, I wish to insert a row above them

I have tried and tried to no avail:

My code:
[vba]
With Range("A1")
.AutoFilter Field:=1, Criteria1:="=*/01"
.Offset(1, 0).SpecialCells(xlCellTypeVisible).Rows.Insert Shift:=xlDown
End With
[/vba]

So I tried the "Recorder" and got this:
[vba]
Selection.AutoFilter Field:=1, Criteria1:="=**/01*", Operator:=xlAnd
Rows("4:70").Select
Selection.SpecialCells(xlCellTypeVisible).Select

Selection.Insert Shift:=xlDown 'Error occurs here
[/vba]

Right after I recorded this, I exicuted the "Recorded" code, it is errors...

Usising Excel 2003

Any help would be greatly appreciated,
Phil

[mbarron]

You're going to have to loop through the results. With a filtered list, you cannot insert rows with more than one "starting point". For example, you would be able to insert rows if you selected rows 6,7, and 8 as one range. You could not, however, insert rows if you Ctrl+Clicked row 6 then 7 then 8.

[Philcjr]

mbarron,

Thanks for your input, but I am struggling to understand how I can do in manually, just selecting the visible rows, right click, and insert... but not through coding...hummmmmm

Looping through each cell, I can do, but it sure would be neat to make it work another way

[mdmackillop]

Remove the AutoFilter first
[VBA]
Sub InsRws()
Dim Rng As Range
Range("A1").AutoFilter Field:=1, Criteria1:="=*/01"
Set Rng = Intersect(Columns(1), ActiveSheet.UsedRange).SpecialCells(xlCellTypeVisible)
Range("A1").AutoFilter
Rng.EntireRow.Insert#
Range("A1").Select
End Sub

[/VBA]

[mbarron]

Okay, now that's odd...

I can insert rows using the Right click, Insert Rows method, but not using the Ctrl + + method (plus key on keyboard). Are none of your results consecutive. If I have 2 consecutive row that are visible, 2 rows are inserted, but the consecutive visible rows are still grouped.

This (where -s are hidden rows)

1
-
1
1
-
1
-
1

results in (where H is a formerly hidden row)

1
H


1
1
H

1
H

1

Is that your desired result? Then again, you may not have consecutive Visible results.

[Philcjr]

Malcom,

This is awesome, THANKS!!!!!!! and is faster than looping, about .5 seconds... What is the purpose of the "#" after the Insert?

Here is my final code
[vba]
With .Range("A1")
' Paste values from Pivot Table
.PasteSpecial xlPasteValues

' Set Filter Criteria
.AutoFilter Field:=1, Criteria1:="=*/12"

' Define Range for visible rows that have been filtered
Set rRange = Intersect(Columns(1), ActiveSheet.UsedRange).Offset(1, 0).SpecialCells(xlCellTypeVisible)

' Turn off the AutoFilter
.AutoFilter

' Insert Rows
rRange.Offset(1, 0).EntireRow.Insert#
End With
[/vba]

[Philcjr]

mbarron,
All the rows that are left visible after the filter, none of them are consecutive... they are like this... 4, 12, 24, 36, 48...

[mdmackillop]

What is the purpose of the "#" after the Insert?

Don't know where that came from!

[djredden73]

This code worked great for me as I was looking to do an insert row after autofiltering (over 10,000 rows affected)..awesome;
only question I have for you is if in the range there are lets say A255:a290 that do not have anything unfiltered (in otherwords they are all displayed in the autofilter based on the selection I chose which was non blanks). When I run the VBA it works on inserting the row on the range where there are gaps between the autofilter; but on the range a255:a290 it inserts 35 rows above row 255 but leaves the range a255:a290 untouched without rows above each. How would I go about rectifying that?

Sub INSRWS()
Dim Rng As Range
Range("a1").AutoFilter Field:=1, Criteria1:="<>"
Set Rng = Intersect(Columns(1), ActiveSheet.UsedRange).SpecialCells(xlCellTypeVisible)
Range("A1").AutoFilter
Rng.EntireRow.Insert
Range("A1").Select
End Sub

Remove the AutoFilter first
[VBA]
Sub InsRws()
Dim Rng As Range
Range("A1").AutoFilter Field:=1, Criteria1:="=*/01"
Set Rng = Intersect(Columns(1), ActiveSheet.UsedRange).SpecialCells(xlCellTypeVisible)
Range("A1").AutoFilter
Rng.EntireRow.Insert#
Range("A1").Select
End Sub

[/VBA]