i have a large set of data when filtered looks like this;
RESOURCE STATUS
User 1 Inactive
User 1 Active
User 2 Inactive
User 2 Inactive
User 2 Active
User 3 Inactive
User 3 Active
i found this below code and tweaked it but nothing is happening
Thank you.
Please Login or Register to view this content.
NON VBA solution
C2 =If(B2="inactive","delete","") and drag down.
After that, filter on column C on delete.
Delete the filtered rows.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
sorry i forgot to mention that some of the INACTIVE data should remain as we use it for looking up their status..
main objective here is remove all inactive status data for each of the users.
if a resource has 3 records and it all says inactive the code should move past it and go to the next person.
RESOURCE STATUS
User 1 Inactive
User 1 Active
HOW TO ATTACH YOUR SAMPLE WORKBOOK:
Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.
"
RESOURCE STATUS
User 1 Inactive
User 1 Active
User 2 Inactive
User 2 Inactive
User 2 Active
User 3 Inactive
User 3 Active
User 4 Active
User 4 Active
User 5 Inactive
User 5 Inactive
"
In your example (With a couple of additional records), which records should the macro delete?
Visit : Excel & Macro
https://www.youtube.com/@CursosDeExcelyMacros
Inactive records on top of active records for each different user. Based on this example its lines 1,3,4 & 6Originally Posted by Dante Amor
It's a bit longer macro, but I guess it's pretty quick. It all depends on the number of records on your sheet and if you have formulas.
Please Login or Register to view this content.
Thanks Dante. it works on a non-tabled data. my current one im planning in using this is on a table i need to refresh everyday.It's a bit longer macro, but I guess it's pretty quick. It all depends on the number of records on your sheet and if you have formulas.
Please Login or Register to view this content.
tried using it but its showing me an error on the last row code
rng.EntireRow.Delete
i tried tweaking it by adding this but it converted to a range
Dim TableRange As ListObject
For Each TableRange In ActiveSheet.ListObjects
TableRange.Unlist
Next TableRange
In your first post you did not mention that you had a table, nor could I appreciate it in your example, nor does your code work with a table.
To adapt the code, you could mention important data such as:
- I have a table.
- Name of the table.
- All the names of the fields in the table.
- The names of the fields with which you are going to work.
- An example of the data.
- An example of the result you want.
- Name of the sheet.
- Initial range of the table, row column.
- Final table range, row column.
What are you wanting to achieve...Upload a sample file as requested in Post 4 and explain in detail what you require...i found this below code and tweaked it but nothing is happening
Good Luck
I don't presume to know what I am doing, however, just like you, I too started somewhere...
One-day, One-problem at a time!!!
If you feel I have helped, please click on the star to left of post [Add Reputation]
Also....add a comment if you like!!!!
And remember...Mark Thread as Solved.
Excel Forum Rocks!!!
i uploaded the sample file. it already has 2 moduules. module 1 finds the duplicate records, highlights and sort them alphabetically.
upon running that 1st module the structure of the data is like this
Annotation 2020-07-29 053349.jpg
what i want to happen
a. if the resource is a duplicate and one record says inactive, delete that inactive row and keep the active one
b. if the resource is a duplicate and has two inactive records and 1 active, delete the inactive row and keep the active one
c. if the resource is a duplicate and both records are inactive, do nothing and skip to the next duplicate record.
d. Keep it as a table. It is connected to a sharepoint list.
Last edited by alfaromeo30; 07-28-2020 at 11:14 PM.
- I have a table.
- Name of the table.
- All the names of the fields in the table.
- The names of the fields with which you are going to work.
- An example of the data.
- An example of the result you want.
- Name of the sheet.
- Initial range of the table, row column.
- Final table range, row column.
You did not answer my questions, it is difficult to assume all the data, but try the following. At the end I put the name of the table: "Table1", you can adjust it to your data.
Please Login or Register to view this content.
Uploaded the sample file and things i want to achieve.
Thanks Dante. Is there a way to keep the table and not convert them to range? this table is connected to a sharepoint list..i wanted to just refresh it to get new data.
As it is already Sorted...Try this...
Please Login or Register to view this content.
Forgive the confusion. I thought you wanted to convert the table into range, because of your code in post #8.Please Login or Register to view this content.
Now you know that you must report if your data is in a range or in a table.
Try the following:
Please Login or Register to view this content.
Last edited by Dante Amor; 07-29-2020 at 08:14 AM.
thanks Dante. this works.
I'm glad to help you. Thanks for the feedback.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks