Prev: MAX IF
Next: How do I get to change my xl document in to readable text or numbe
From: Paula on 18 May 2010 04:55
Hi,

Hope you can help me, I have a report which I need to delete some lines of
data. The criteria is as follows:

Each record can have numerous lines of data and has a unique reference i.e.
MEBACI, however if this record contains any of 9 conditions in column M the
record should be left in full with no deleted lines of data.

However if the record does not contain any of the 9 conditions the entire
record should be deleted from the report.

The 9 conditions are Countires, e.g. UK, France, Germany etc.

Is there any formula which can be used to detect this?

Regards
Paula


From: Mike H on 18 May 2010 05:38
Paula,

Are you ready for a Macro. Alt+F11 to open VB editor. Right click
'ThisWorkbook' and insert modula and paste the code below in.

Edit these 2 lines
Set sht = Sheets("Sheet1") ' change to suit
S = "France,Germany,Spain,UK" ' add as required

To the correct sheet and the correct countries. Add the necessaey countries
sperated by a comma and NO spaces. Run the code.


Sub Marine()
Dim R As Range
Dim V As Variant
Dim S As String
Dim CopyRange As Range
Set sht = Sheets("Sheet1") ' change to suit
LastRow = sht.Cells(Cells.Rows.Count, "M").End(xlUp).Row
S = "France,Germany,Spain,UK" ' add as required
V = Split(S, ",")
For Each R In sht.Range("M1:M" & 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.


"Paula" wrote:

> Hi,
>
> Hope you can help me, I have a report which I need to delete some lines of
> data. The criteria is as follows:
>
> Each record can have numerous lines of data and has a unique reference i.e.
> MEBACI, however if this record contains any of 9 conditions in column M the
> record should be left in full with no deleted lines of data.
>
> However if the record does not contain any of the 9 conditions the entire
> record should be deleted from the report.
>
> The 9 conditions are Countires, e.g. UK, France, Germany etc.
>
> Is there any formula which can be used to detect this?
>
> Regards
> Paula
>
>
From: Paula on 18 May 2010 05:58
Hi Mike,

I am not very familiar with macros, well not at all, is there any way just
to do this with formula?

Regards

Paula


"Mike H" wrote:

> Paula,
>
> Are you ready for a Macro. Alt+F11 to open VB editor. Right click
> 'ThisWorkbook' and insert modula and paste the code below in.
>
> Edit these 2 lines
> Set sht = Sheets("Sheet1") ' change to suit
> S = "France,Germany,Spain,UK" ' add as required
>
> To the correct sheet and the correct countries. Add the necessaey countries
> sperated by a comma and NO spaces. Run the code.
>
>
> Sub Marine()
> Dim R As Range
> Dim V As Variant
> Dim S As String
> Dim CopyRange As Range
> Set sht = Sheets("Sheet1") ' change to suit
> LastRow = sht.Cells(Cells.Rows.Count, "M").End(xlUp).Row
> S = "France,Germany,Spain,UK" ' add as required
> V = Split(S, ",")
> For Each R In sht.Range("M1:M" & 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.
>
>
> "Paula" wrote:
>
> > Hi,
> >
> > Hope you can help me, I have a report which I need to delete some lines of
> > data. The criteria is as follows:
> >
> > Each record can have numerous lines of data and has a unique reference i.e.
> > MEBACI, however if this record contains any of 9 conditions in column M the
> > record should be left in full with no deleted lines of data.
> >
> > However if the record does not contain any of the 9 conditions the entire
> > record should be deleted from the report.
> >
> > The 9 conditions are Countires, e.g. UK, France, Germany etc.
> >
> > Is there any formula which can be used to detect this?
> >
> > Regards
> > Paula
> >
> >
From: Paula on 18 May 2010 06:00
Hi Mike,

I'm not familiar with macros, is there any way to do this with formula?

Rgds Paula


"Mike H" wrote:

> Paula,
>
> Are you ready for a Macro. Alt+F11 to open VB editor. Right click
> 'ThisWorkbook' and insert modula and paste the code below in.
>
> Edit these 2 lines
> Set sht = Sheets("Sheet1") ' change to suit
> S = "France,Germany,Spain,UK" ' add as required
>
> To the correct sheet and the correct countries. Add the necessaey countries
> sperated by a comma and NO spaces. Run the code.
>
>
> Sub Marine()
> Dim R As Range
> Dim V As Variant
> Dim S As String
> Dim CopyRange As Range
> Set sht = Sheets("Sheet1") ' change to suit
> LastRow = sht.Cells(Cells.Rows.Count, "M").End(xlUp).Row
> S = "France,Germany,Spain,UK" ' add as required
> V = Split(S, ",")
> For Each R In sht.Range("M1:M" & 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.
>
>
> "Paula" wrote:
>
> > Hi,
> >
> > Hope you can help me, I have a report which I need to delete some lines of
> > data. The criteria is as follows:
> >
> > Each record can have numerous lines of data and has a unique reference i.e.
> > MEBACI, however if this record contains any of 9 conditions in column M the
> > record should be left in full with no deleted lines of data.
> >
> > However if the record does not contain any of the 9 conditions the entire
> > record should be deleted from the report.
> >
> > The 9 conditions are Countires, e.g. UK, France, Germany etc.
> >
> > Is there any formula which can be used to detect this?
> >
> > Regards
> > Paula
> >
> >
From: Paula on 18 May 2010 06:01

Hi Mike

I am not familiar with macros, is there any way to do this with formulas?

Regards
Paula

"Mike H" wrote:

> Paula,
>
> Are you ready for a Macro. Alt+F11 to open VB editor. Right click
> 'ThisWorkbook' and insert modula and paste the code below in.
>
> Edit these 2 lines
> Set sht = Sheets("Sheet1") ' change to suit
> S = "France,Germany,Spain,UK" ' add as required
>
> To the correct sheet and the correct countries. Add the necessaey countries
> sperated by a comma and NO spaces. Run the code.
>
>
> Sub Marine()
> Dim R As Range
> Dim V As Variant
> Dim S As String
> Dim CopyRange As Range
> Set sht = Sheets("Sheet1") ' change to suit
> LastRow = sht.Cells(Cells.Rows.Count, "M").End(xlUp).Row
> S = "France,Germany,Spain,UK" ' add as required
> V = Split(S, ",")
> For Each R In sht.Range("M1:M" & 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.
>
>
> "Paula" wrote:
>
> > Hi,
> >
> > Hope you can help me, I have a report which I need to delete some lines of
> > data. The criteria is as follows:
> >
> > Each record can have numerous lines of data and has a unique reference i.e.
> > MEBACI, however if this record contains any of 9 conditions in column M the
> > record should be left in full with no deleted lines of data.
> >
> > However if the record does not contain any of the 9 conditions the entire
> > record should be deleted from the report.
> >
> > The 9 conditions are Countires, e.g. UK, France, Germany etc.
> >
> > Is there any formula which can be used to detect this?
> >
> > Regards
> > Paula
> >
> >
 |  Next  |  Last
Pages: 1 2
Prev: MAX IF
Next: How do I get to change my xl document in to readable text or numbe