selection.insert shift:=xldown error when autofilter is on

Question

I have a sheet that has some filtered rows as per the code snippet below:

.Select

With .Range("A4:" & colRef & rowCount)

.AutoFilter

.AutoFilter Field:=FilterFieldYear, Criteria1:=strYear

.AutoFilter Field:=FilterFieldRegDiv, Criteria1:=strReg

.AutoFilter Field:=FilterFieldProgram, Criteria1:=strProg

End With

In another piece of code I get the error "insert method of range class failed" on the last line of code in the next code snippet.

Sheets("Action Plan").Activate

Sheets("Action Plan").Unprotect

Stop 'check if we need to add 1 to rowlocation when rows are filtered

Range("A" & rowLocation & ":" & colRef & rowLocation).Select

Selection.Insert Shift:=xlDown

So, how can I insert some cells if the above cells have an autofileter on it???

Answer 1

Hi,

add this

.Select

With .Range("A4:" & colRef & rowCount)

.AutoFilter

.AutoFilter Field:=FilterFieldYear, Criteria1:=strYear

.AutoFilter Field:=FilterFieldRegDiv, Criteria1:=strReg

.AutoFilter Field:=FilterFieldProgram, Criteria1:=strProg

End With

---

Range("A4").AutoFilter

.............

............

[Edit]

ok,

HansV replied to you.

Answer 2

Turn off the AutoFilter, insert cells, then turn on the filter again.

Answer 3

Thanks Hans and Tasos - worked a treat.