extracting specific rows
 

Looking for help with the following:

Numeric and non numeric data is in A2:F1580, A1:F1 is the header row.
Column C has different names and surnames in one cell, separated by a space.
Some cells have only names, some cells have names, middlenames and surnames
and some have only names and surnames. I would like to extract rows to
another sheet where cells in column C do either have only one name or more
than one name.
Any ideas in terms of a macro or function?

Regards

extracting specific rows
 

I would like to extract rows to
another sheet where cells in column C do either have only one name or more
than one name.

That means eveything, what is it you want to extract?

Mike


"wynand" wrote:

Looking for help with the following:

Numeric and non numeric data is in A2:F1580, A1:F1 is the header row.
Column C has different names and surnames in one cell, separated by a space.
Some cells have only names, some cells have names, middlenames and surnames
and some have only names and surnames. I would like to extract rows to
another sheet where cells in column C do either have only one name or more
than one name.
Any ideas in terms of a macro or function?

Regards

extracting specific rows
 

the sheet is copied from selected sent emails in lotus notes
I want to divide internal sent emails from external sent emails.
Lotus is set up so that in Column C (Header named 'Who') Internal emails
shows names and surnames. External emails show only names.
I want to ectract rows with only one name in Column C

"Mike H" wrote:

I would like to extract rows to
another sheet where cells in column C do either have only one name or more
than one name.

That means eveything, what is it you want to extract?

Mike


"wynand" wrote:

Looking for help with the following:

Numeric and non numeric data is in A2:F1580, A1:F1 is the header row.
Column C has different names and surnames in one cell, separated by a space.
Some cells have only names, some cells have names, middlenames and surnames
and some have only names and surnames. I would like to extract rows to
another sheet where cells in column C do either have only one name or more
than one name.
Any ideas in terms of a macro or function?

Regards

extracting specific rows
 

Alt+F11 to open editor, right click 'This Workbook' insert module and paste
this in.

Sub stance()
Dim myrange, copyrange As Range
Sheets("Sheet1").Select 'Change to suit
LastRow = Cells(Cells.Rows.Count, "C").End(xlUp).Row
Set myrange = Range("C1:C" & LastRow)
For Each C In myrange
With Application.WorksheetFunction
numnames = Len(.Trim(C.Text)) - Len(.Substitute(.Trim(C), " ", "")) + 1
End With
If numnames = 1 Then
If copyrange Is Nothing Then
Set copyrange = C.EntireRow
Else
Set copyrange = Union(copyrange, C.EntireRow)
End If
End If
Next
copyrange.Copy
Sheets("Sheet2").Select 'Change to suit
Cells(1, 1).Select
ActiveSheet.Paste
End Sub

Mike

"wynand" wrote:

the sheet is copied from selected sent emails in lotus notes
I want to divide internal sent emails from external sent emails.
Lotus is set up so that in Column C (Header named 'Who') Internal emails
shows names and surnames. External emails show only names.
I want to ectract rows with only one name in Column C

"Mike H" wrote:

I would like to extract rows to
another sheet where cells in column C do either have only one name or more
than one name.

That means eveything, what is it you want to extract?

Mike


"wynand" wrote:

Looking for help with the following:

Numeric and non numeric data is in A2:F1580, A1:F1 is the header row.
Column C has different names and surnames in one cell, separated by a space.
Some cells have only names, some cells have names, middlenames and surnames
and some have only names and surnames. I would like to extract rows to
another sheet where cells in column C do either have only one name or more
than one name.
Any ideas in terms of a macro or function?

Regards

extracting specific rows
 

Can you please explain steps after module has been pasted
I seem to be doing something wrong

"Mike H" wrote:

Alt+F11 to open editor, right click 'This Workbook' insert module and paste
this in.

Sub stance()
Dim myrange, copyrange As Range
Sheets("Sheet1").Select 'Change to suit
LastRow = Cells(Cells.Rows.Count, "C").End(xlUp).Row
Set myrange = Range("C1:C" & LastRow)
For Each C In myrange
With Application.WorksheetFunction
numnames = Len(.Trim(C.Text)) - Len(.Substitute(.Trim(C), " ", "")) + 1
End With
If numnames = 1 Then
If copyrange Is Nothing Then
Set copyrange = C.EntireRow
Else
Set copyrange = Union(copyrange, C.EntireRow)
End If
End If
Next
copyrange.Copy
Sheets("Sheet2").Select 'Change to suit
Cells(1, 1).Select
ActiveSheet.Paste
End Sub

Mike

"wynand" wrote:

the sheet is copied from selected sent emails in lotus notes
I want to divide internal sent emails from external sent emails.
Lotus is set up so that in Column C (Header named 'Who') Internal emails
shows names and surnames. External emails show only names.
I want to ectract rows with only one name in Column C

"Mike H" wrote:

I would like to extract rows to
another sheet where cells in column C do either have only one name or more
than one name.

That means eveything, what is it you want to extract?

Mike


"wynand" wrote:

Looking for help with the following:

Numeric and non numeric data is in A2:F1580, A1:F1 is the header row.
Column C has different names and surnames in one cell, separated by a space.
Some cells have only names, some cells have names, middlenames and surnames
and some have only names and surnames. I would like to extract rows to
another sheet where cells in column C do either have only one name or more
than one name.
Any ideas in terms of a macro or function?

Regards

extracting specific rows
 

Hi,

When you've pasted it into a module you need to change the sheet names

Sheet1 gets changed to the sheet you want to copy from
Sheet2 gets changed to the sheet you want to paste to
Both sheet names must be in quotes as they are now.

When you've done that tap F5

Mike

"wynand" wrote:

Can you please explain steps after module has been pasted
I seem to be doing something wrong

"Mike H" wrote:

Alt+F11 to open editor, right click 'This Workbook' insert module and paste
this in.

Sub stance()
Dim myrange, copyrange As Range
Sheets("Sheet1").Select 'Change to suit
LastRow = Cells(Cells.Rows.Count, "C").End(xlUp).Row
Set myrange = Range("C1:C" & LastRow)
For Each C In myrange
With Application.WorksheetFunction
numnames = Len(.Trim(C.Text)) - Len(.Substitute(.Trim(C), " ", "")) + 1
End With
If numnames = 1 Then
If copyrange Is Nothing Then
Set copyrange = C.EntireRow
Else
Set copyrange = Union(copyrange, C.EntireRow)
End If
End If
Next
copyrange.Copy
Sheets("Sheet2").Select 'Change to suit
Cells(1, 1).Select
ActiveSheet.Paste
End Sub

Mike

"wynand" wrote:

the sheet is copied from selected sent emails in lotus notes
I want to divide internal sent emails from external sent emails.
Lotus is set up so that in Column C (Header named 'Who') Internal emails
shows names and surnames. External emails show only names.
I want to ectract rows with only one name in Column C

"Mike H" wrote:

I would like to extract rows to
another sheet where cells in column C do either have only one name or more
than one name.

That means eveything, what is it you want to extract?

Mike


"wynand" wrote:

Looking for help with the following:

Numeric and non numeric data is in A2:F1580, A1:F1 is the header row.
Column C has different names and surnames in one cell, separated by a space.
Some cells have only names, some cells have names, middlenames and surnames
and some have only names and surnames. I would like to extract rows to
another sheet where cells in column C do either have only one name or more
than one name.
Any ideas in terms of a macro or function?

Regards

extracting specific rows
 

Mike

I'm still not coming right, the sheet names are named as normal (1,2,3)
F5 takes me to a go to dialogue box with no results

"Mike H" wrote:

Hi,

When you've pasted it into a module you need to change the sheet names

Sheet1 gets changed to the sheet you want to copy from
Sheet2 gets changed to the sheet you want to paste to
Both sheet names must be in quotes as they are now.

When you've done that tap F5

Mike

"wynand" wrote:

Can you please explain steps after module has been pasted
I seem to be doing something wrong

"Mike H" wrote:

Alt+F11 to open editor, right click 'This Workbook' insert module and paste
this in.

Sub stance()
Dim myrange, copyrange As Range
Sheets("Sheet1").Select 'Change to suit
LastRow = Cells(Cells.Rows.Count, "C").End(xlUp).Row
Set myrange = Range("C1:C" & LastRow)
For Each C In myrange
With Application.WorksheetFunction
numnames = Len(.Trim(C.Text)) - Len(.Substitute(.Trim(C), " ", "")) + 1
End With
If numnames = 1 Then
If copyrange Is Nothing Then
Set copyrange = C.EntireRow
Else
Set copyrange = Union(copyrange, C.EntireRow)
End If
End If
Next
copyrange.Copy
Sheets("Sheet2").Select 'Change to suit
Cells(1, 1).Select
ActiveSheet.Paste
End Sub

Mike

"wynand" wrote:

the sheet is copied from selected sent emails in lotus notes
I want to divide internal sent emails from external sent emails.
Lotus is set up so that in Column C (Header named 'Who') Internal emails
shows names and surnames. External emails show only names.
I want to ectract rows with only one name in Column C

"Mike H" wrote:

I would like to extract rows to
another sheet where cells in column C do either have only one name or more
than one name.

That means eveything, what is it you want to extract?

Mike


"wynand" wrote:

Looking for help with the following:

Numeric and non numeric data is in A2:F1580, A1:F1 is the header row.
Column C has different names and surnames in one cell, separated by a space.
Some cells have only names, some cells have names, middlenames and surnames
and some have only names and surnames. I would like to extract rows to
another sheet where cells in column C do either have only one name or more
than one name.
Any ideas in terms of a macro or function?

Regards

extracting specific rows
 

Iy sounds like your on the worksheet hen tapping F5

You tap F5 while in VB editor with the cursor within the subroutine or you
can run it from the worksheet by clicking

Tools|Macro|Macros
Select the name of the macro and click run

Mike

"wynand" wrote:

Mike

I'm still not coming right, the sheet names are named as normal (1,2,3)
F5 takes me to a go to dialogue box with no results

"Mike H" wrote:

Hi,

When you've pasted it into a module you need to change the sheet names

Sheet1 gets changed to the sheet you want to copy from
Sheet2 gets changed to the sheet you want to paste to
Both sheet names must be in quotes as they are now.

When you've done that tap F5

Mike

"wynand" wrote:

Can you please explain steps after module has been pasted
I seem to be doing something wrong

"Mike H" wrote:

Alt+F11 to open editor, right click 'This Workbook' insert module and paste
this in.

Sub stance()
Dim myrange, copyrange As Range
Sheets("Sheet1").Select 'Change to suit
LastRow = Cells(Cells.Rows.Count, "C").End(xlUp).Row
Set myrange = Range("C1:C" & LastRow)
For Each C In myrange
With Application.WorksheetFunction
numnames = Len(.Trim(C.Text)) - Len(.Substitute(.Trim(C), " ", "")) + 1
End With
If numnames = 1 Then
If copyrange Is Nothing Then
Set copyrange = C.EntireRow
Else
Set copyrange = Union(copyrange, C.EntireRow)
End If
End If
Next
copyrange.Copy
Sheets("Sheet2").Select 'Change to suit
Cells(1, 1).Select
ActiveSheet.Paste
End Sub

Mike

"wynand" wrote:

the sheet is copied from selected sent emails in lotus notes
I want to divide internal sent emails from external sent emails.
Lotus is set up so that in Column C (Header named 'Who') Internal emails
shows names and surnames. External emails show only names.
I want to ectract rows with only one name in Column C

"Mike H" wrote:

I would like to extract rows to
another sheet where cells in column C do either have only one name or more
than one name.

That means eveything, what is it you want to extract?

Mike


"wynand" wrote:

Looking for help with the following:

Numeric and non numeric data is in A2:F1580, A1:F1 is the header row.
Column C has different names and surnames in one cell, separated by a space.
Some cells have only names, some cells have names, middlenames and surnames
and some have only names and surnames. I would like to extract rows to
another sheet where cells in column C do either have only one name or more
than one name.
Any ideas in terms of a macro or function?

Regards

extracting specific rows
 

Brilliant Mike, initially gave me "syntax error" after F5, but I pasted into
a new sheet and now it works.

Thank you

"Mike H" wrote:

Iy sounds like your on the worksheet hen tapping F5

You tap F5 while in VB editor with the cursor within the subroutine or you
can run it from the worksheet by clicking

Tools|Macro|Macros
Select the name of the macro and click run

Mike

"wynand" wrote:

Mike

I'm still not coming right, the sheet names are named as normal (1,2,3)
F5 takes me to a go to dialogue box with no results

"Mike H" wrote:

Hi,

When you've pasted it into a module you need to change the sheet names

Sheet1 gets changed to the sheet you want to copy from
Sheet2 gets changed to the sheet you want to paste to
Both sheet names must be in quotes as they are now.

When you've done that tap F5

Mike

"wynand" wrote:

Can you please explain steps after module has been pasted
I seem to be doing something wrong

"Mike H" wrote:

Alt+F11 to open editor, right click 'This Workbook' insert module and paste
this in.

Sub stance()
Dim myrange, copyrange As Range
Sheets("Sheet1").Select 'Change to suit
LastRow = Cells(Cells.Rows.Count, "C").End(xlUp).Row
Set myrange = Range("C1:C" & LastRow)
For Each C In myrange
With Application.WorksheetFunction
numnames = Len(.Trim(C.Text)) - Len(.Substitute(.Trim(C), " ", "")) + 1
End With
If numnames = 1 Then
If copyrange Is Nothing Then
Set copyrange = C.EntireRow
Else
Set copyrange = Union(copyrange, C.EntireRow)
End If
End If
Next
copyrange.Copy
Sheets("Sheet2").Select 'Change to suit
Cells(1, 1).Select
ActiveSheet.Paste
End Sub

Mike

"wynand" wrote:

the sheet is copied from selected sent emails in lotus notes
I want to divide internal sent emails from external sent emails.
Lotus is set up so that in Column C (Header named 'Who') Internal emails
shows names and surnames. External emails show only names.
I want to ectract rows with only one name in Column C

"Mike H" wrote:

I would like to extract rows to
another sheet where cells in column C do either have only one name or more
than one name.

That means eveything, what is it you want to extract?

Mike


"wynand" wrote:

Looking for help with the following:

Numeric and non numeric data is in A2:F1580, A1:F1 is the header row.
Column C has different names and surnames in one cell, separated by a space.
Some cells have only names, some cells have names, middlenames and surnames
and some have only names and surnames. I would like to extract rows to
another sheet where cells in column C do either have only one name or more
than one name.
Any ideas in terms of a macro or function?

Regards

extracting specific rows
 

We got there in the end, thanks for the feedback

"wynand" wrote:

Brilliant Mike, initially gave me "syntax error" after F5, but I pasted into
a new sheet and now it works.

Thank you

"Mike H" wrote:

Iy sounds like your on the worksheet hen tapping F5

You tap F5 while in VB editor with the cursor within the subroutine or you
can run it from the worksheet by clicking

Tools|Macro|Macros
Select the name of the macro and click run

Mike

"wynand" wrote:

Mike

I'm still not coming right, the sheet names are named as normal (1,2,3)
F5 takes me to a go to dialogue box with no results

"Mike H" wrote:

Hi,

When you've pasted it into a module you need to change the sheet names

Sheet1 gets changed to the sheet you want to copy from
Sheet2 gets changed to the sheet you want to paste to
Both sheet names must be in quotes as they are now.

When you've done that tap F5

Mike

"wynand" wrote:

Can you please explain steps after module has been pasted
I seem to be doing something wrong

"Mike H" wrote:

Alt+F11 to open editor, right click 'This Workbook' insert module and paste
this in.

Sub stance()
Dim myrange, copyrange As Range
Sheets("Sheet1").Select 'Change to suit
LastRow = Cells(Cells.Rows.Count, "C").End(xlUp).Row
Set myrange = Range("C1:C" & LastRow)
For Each C In myrange
With Application.WorksheetFunction
numnames = Len(.Trim(C.Text)) - Len(.Substitute(.Trim(C), " ", "")) + 1
End With
If numnames = 1 Then
If copyrange Is Nothing Then
Set copyrange = C.EntireRow
Else
Set copyrange = Union(copyrange, C.EntireRow)
End If
End If
Next
copyrange.Copy
Sheets("Sheet2").Select 'Change to suit
Cells(1, 1).Select
ActiveSheet.Paste
End Sub

Mike

"wynand" wrote:

the sheet is copied from selected sent emails in lotus notes
I want to divide internal sent emails from external sent emails.
Lotus is set up so that in Column C (Header named 'Who') Internal emails
shows names and surnames. External emails show only names.
I want to ectract rows with only one name in Column C

"Mike H" wrote:

I would like to extract rows to
another sheet where cells in column C do either have only one name or more
than one name.

That means eveything, what is it you want to extract?

Mike


"wynand" wrote:

Looking for help with the following:

Numeric and non numeric data is in A2:F1580, A1:F1 is the header row.
Column C has different names and surnames in one cell, separated by a space.
Some cells have only names, some cells have names, middlenames and surnames
and some have only names and surnames. I would like to extract rows to
another sheet where cells in column C do either have only one name or more
than one name.
Any ideas in terms of a macro or function?

Regards