Re: $$Excel-Macros$$ macro: delete base on table
Hi Guys, thanks a lot for your help. regards, Pawel From: Rajan_Verma rajanverma1...@gmail.com To: excel-macros@googlegroups.com Sent: Thursday, October 4, 2012 6:32 PM Subject: FW: $$Excel-Macros$$ macro: delete base on table Here is thread on this Solution , Please visit http://www.excelfox.com/forum/f20/delete-list-contain-matching-second-list-589/ Regards Rajan verma +91 7838100659 [IM-Gtalk] From:Rajan_Verma [mailto:rajanverma1...@gmail.com] Sent: 04 October 2012 9:58 To: 'excel-macros@googlegroups.com' Subject: RE: $$Excel-Macros$$ macro: delete base on table Hi, Use this Code, Just Create Two Name Ranges, Sheet1!B2=”rngRange” Sheet2!B2 =” MapDelete” Sub ExcludeFromList() Dim objDicMap As Object Dim VarArrData Dim VarArrResult Dim rngCell As Range Dim lngCOunt As Long VarArrData = Intersect(Range(rngRange).CurrentRegion, Range(rngRange).CurrentRegion.Offset(1)) Set objDicMap = CreateObject(Scripting.Dictionary) ' Dictionary Object get Mapping 'Filling Dictionary For Each rngCell In Intersect(Range(MapDelete).CurrentRegion, Range(MapDelete).CurrentRegion.Offset(1)) If Not objDicMap.exists(rngCell.Value) Then objDicMap.Add rngCell.Value, rngCell.Value Next rngCell 'Filling Result Array From Data List which would not Include Mapping Data For lngCOunt = LBound(VarArrData) To UBound(VarArrData) If Not objDicMap.exists(VarArrData(lngCOunt, 1)) Then If Not IsArray(VarArrResult) Then ReDim VarArrResult(0 To 0) VarArrResult(0) = VarArrData(lngCOunt, 1) Else ReDim Preserve VarArrResult(UBound(VarArrResult) + 1) VarArrResult(UBound(VarArrResult)) = VarArrData(lngCOunt, 1) End If End If Next lngCOunt 'Clear Old List Range(rngRange).CurrentRegion.Offset(1).Clear 'Replace with new list Range(rngRange).Offset(1).Resize(UBound(VarArrResult)).Value = Application.Transpose(VarArrResult) Set objDicMap = Nothing Set rngCell = Nothing End Sub Keep Excelling J Regards Rajan verma +91 7838100659 [IM-Gtalk] From:excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of pawel lupinski Sent: 04 October 2012 9:30 To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ macro: delete base on table Hi All, I'd like to ask you for favour, can you help me with these. I have a table code with codes. From this table I'd like to excclude all codes for table to delete (tab table). Right now I'm using or function in vba, but when I want to add something, each time I need to open VB and change it. Is there any way that I can manage it from to delete table? Thanks Pawel -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post
Re: $$Excel-Macros$$ macro: delete base on table
Sub sample_code() Application.ScreenUpdating = False Dim a As Range Dim SrchRnga As Range Dim i As Long Set SrchRnga = Sheets(1).Range(b1: b Sheets(1).Range(b65356).End(xlUp).Row) For i = 3 To Sheets(table).Range(b65356).End(xlUp).Row Do Set a = SrchRnga.Find(Sheets(table).Range(b i).Value, LookIn:=xlValues) If Not a Is Nothing Then a.EntireRow.Delete Loop While Not a Is Nothing Next Application.ScreenUpdating = True End Sub On Thu, Oct 4, 2012 at 9:30 PM, pawel lupinski lupins...@yahoo.com wrote: Hi All,** I'd like to ask you for favour, can you help me with these. I have a table code with codes. From this table I'd like to excclude all codes for table to delete (tab table). Right now I'm using or function in vba, but when I want to add something, each time I need to open VB and change it. Is there any way that I can manage it from to delete table? Thanks Pawel -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. -- *Regards* * * *Ashish Koul* *Visit* *http://www.excelvbamacros.com/* *http://www.accessvbamacros.com/* P Before printing, think about the environment. -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com.
RE: $$Excel-Macros$$ macro: delete base on table
Hi, Use this Code, Just Create Two Name Ranges, Sheet1!B2=rngRange Sheet2!B2 = MapDelete Sub ExcludeFromList() Dim objDicMap As Object Dim VarArrData Dim VarArrResult Dim rngCell As Range Dim lngCOunt As Long VarArrData = Intersect(Range(rngRange).CurrentRegion, Range(rngRange).CurrentRegion.Offset(1)) Set objDicMap = CreateObject(Scripting.Dictionary)' Dictionary Object get Mapping 'Filling Dictionary For Each rngCell In Intersect(Range(MapDelete).CurrentRegion, Range(MapDelete).CurrentRegion.Offset(1)) If Not objDicMap.exists(rngCell.Value) Then objDicMap.Add rngCell.Value, rngCell.Value Next rngCell 'Filling Result Array From Data List which would not Include Mapping Data For lngCOunt = LBound(VarArrData) To UBound(VarArrData) If Not objDicMap.exists(VarArrData(lngCOunt, 1)) Then If Not IsArray(VarArrResult) Then ReDim VarArrResult(0 To 0) VarArrResult(0) = VarArrData(lngCOunt, 1) Else ReDim Preserve VarArrResult(UBound(VarArrResult) + 1) VarArrResult(UBound(VarArrResult)) = VarArrData(lngCOunt, 1) End If End If Next lngCOunt 'Clear Old List Range(rngRange).CurrentRegion.Offset(1).Clear 'Replace with new list Range(rngRange).Offset(1).Resize(UBound(VarArrResult)).Value = Application.Transpose(VarArrResult) Set objDicMap = Nothing Set rngCell = Nothing End Sub Keep Excelling J Regards Rajan verma +91 7838100659 [IM-Gtalk] From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of pawel lupinski Sent: 04 October 2012 9:30 To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ macro: delete base on table Hi All, I'd like to ask you for favour, can you help me with these. I have a table code with codes. From this table I'd like to excclude all codes for table to delete (tab table). Right now I'm using or function in vba, but when I want to add something, each time I need to open VB and change it. Is there any way that I can manage it from to delete table? Thanks Pawel -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com.
