Solved

Filter and delete

Posted on 2016-11-16
6
20 Views
Last Modified: 2016-11-17
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
0
Comment
Question by:Jagwarman
  • 5
6 Comments
 
LVL 49

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 41890960
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
 

Author Comment

by:Jagwarman
ID: 41890989
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
 

Author Comment

by:Jagwarman
ID: 41891023
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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 

Author Comment

by:Jagwarman
ID: 41891030
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
 

Author Comment

by:Jagwarman
ID: 41891034
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
 

Author Closing Comment

by:Jagwarman
ID: 41891035
:-)
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question