Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extracting characters before or after a specific character | Excel Worksheet Functions | |||
extracting specific rows | Excel Discussion (Misc queries) | |||
Extracting specific word in a cell | Excel Worksheet Functions | |||
Extracting specific data from a cell | Excel Worksheet Functions | |||
Pivot Table - Extracting specific data | Excel Worksheet Functions |