Help in macro coding
in [General]
|
From: Sasikiran on 24 Mar 2010 08:49 Dear, I am working on a macro which trims the raw data into a desired one. i would require your help in adding the macro code to delete the entire row if it does not matches to my requirement. Say Column D has data which gives the name of the individuals. I would only require the rows which has the below mentioned names in the column D. Like John, Peter, Sandra and Kate. The macro code should identify these names, keep the rows having these and delete all other rows which do not match to these names. Please help.
From: Don Guillett on 24 Mar 2010 09:17 As ALWAYS, post your coding efforts for comments and suggestions. -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett(a)gmail.com "Sasikiran" <Sasikiran(a)discussions.microsoft.com> wrote in message news:7F3CDB4F-24A9-4376-A601-1B55AA1864AC(a)microsoft.com... > Dear, > > I am working on a macro which trims the raw data into a desired one. > > i would require your help in adding the macro code to delete the entire > row > if it does not matches to my requirement. > > Say Column D has data which gives the name of the individuals. I would > only > require the rows which has the below mentioned names in the column D. Like > John, Peter, Sandra and Kate. > > The macro code should identify these names, keep the rows having these and > delete all other rows which do not match to these names. > > Please help.
From: Mike H on 24 Mar 2010 09:50 Hi, try this Sub Versive() Dim R As Range Dim V As Variant Dim S As String Dim CopyRange As Range Dim LastRow As Long Set Sht = Sheets("Sheet1") ' Change to suit LastRow = Sht.Cells(Cells.Rows.Count, "D").End(xlUp).Row S = "Peter,Sandra,Kate" V = Split(S, ",") For Each R In Sht.Range("D1:D" & LastRow) If IsError(Application.Match(CStr(R.Value), V, 0)) Then If CopyRange Is Nothing Then Set CopyRange = R.EntireRow Else Set CopyRange = Union(CopyRange, R.EntireRow) End If End If Next R If Not CopyRange Is Nothing Then CopyRange.Delete End If End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Sasikiran" wrote: > Dear, > > I am working on a macro which trims the raw data into a desired one. > > i would require your help in adding the macro code to delete the entire row > if it does not matches to my requirement. > > Say Column D has data which gives the name of the individuals. I would only > require the rows which has the below mentioned names in the column D. Like > John, Peter, Sandra and Kate. > > The macro code should identify these names, keep the rows having these and > delete all other rows which do not match to these names. > > Please help.
From: Sasikiran on 24 Mar 2010 10:34 Thank you so much it is working :) "Mike H" wrote: > Hi, > > try this > > Sub Versive() > Dim R As Range > Dim V As Variant > Dim S As String > Dim CopyRange As Range > Dim LastRow As Long > Set Sht = Sheets("Sheet1") ' Change to suit > LastRow = Sht.Cells(Cells.Rows.Count, "D").End(xlUp).Row > S = "Peter,Sandra,Kate" > V = Split(S, ",") > For Each R In Sht.Range("D1:D" & LastRow) > If IsError(Application.Match(CStr(R.Value), V, 0)) Then > If CopyRange Is Nothing Then > Set CopyRange = R.EntireRow > Else > Set CopyRange = Union(CopyRange, R.EntireRow) > End If > End If > Next R > > If Not CopyRange Is Nothing Then > CopyRange.Delete > End If > > End Sub > > -- > Mike > > When competing hypotheses are otherwise equal, adopt the hypothesis that > introduces the fewest assumptions while still sufficiently answering the > question. > > > "Sasikiran" wrote: > > > Dear, > > > > I am working on a macro which trims the raw data into a desired one. > > > > i would require your help in adding the macro code to delete the entire row > > if it does not matches to my requirement. > > > > Say Column D has data which gives the name of the individuals. I would only > > require the rows which has the below mentioned names in the column D. Like > > John, Peter, Sandra and Kate. > > > > The macro code should identify these names, keep the rows having these and > > delete all other rows which do not match to these names. > > > > Please help.
|
Pages: 1 Prev: text value Next: pasting values from pivot table still linked |