Filter and delete

Can an expert provide me with VBA code that will filter then delete the data in that range but only that range.

So the range is A-N. I need to filter Column ‘I’ with Credit and then delete anything that now appears in that filter then unfilter.

There is a pivot table in cells R-S and I do not want to delete this.

I am using below code on other sheets where there is no pivot and this works fine but it deletes entire row.

Thank you in advance
JagwarmanAsked:
Who is Participating?
 
Rgonzo1971Connect With a Mentor Commented:
Hi,

pls try

Sub Macro1()
'
' Macro1 Macro
'
   Set c = Range("I" & Rows.Count)
   For lCol = 1 To WorksheetFunction.CountIf(Range("I:I"), "Credit")
        Set c = Range("I:I").Find(What:="Credit", LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False)
            Range("A" & c.Row).Resize(, 14).Delete Shift:=xlShiftUp
   Next lCol
 
End Sub

Open in new window

Regards
0
 
JagwarmanAuthor Commented:
I don't understand it is giving me error "You cannot move a part of a pivot table and yet you are only looking upto 14 and the pivot is in 18 and 19. Any ideas?


 Range("A" & c.Row).Resize(, 14).Delete Shift:=xlShiftUp
0
 
JagwarmanAuthor Commented:
what I notice is if I right click my mouse on the range it does not show 'Delete' which would allow me to Shift cells UP/Down/Right/Left it only give me the option to Delete Row ???
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
JagwarmanAuthor Commented:
reading up on this on Google looks like it's not possible it says you can only delete entire row when in filter mode. So now I have the problem I only want to delete from A-n where Credit is in column 'I'

What is the way around this?

Sorry Rgonzo, soon I will be gone
0
 
JagwarmanAuthor Commented:
Rgonzo, I think it is time for me to retire. Of course it was me, I just needed to remove the filter.

Regards
Jagwarman
0
 
JagwarmanAuthor Commented:
:-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.