Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > extracting specific rows
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 54
Default 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 does either have only one name or more
than one name.
Any ideas in terms of a macro or function?

Regards
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 9,101
Default extracting specific rows
Sub extractsinglenames()

NewRowCount = 2
With ActiveSheet
LastRow = .Range("C" & Rows.Count).End(xlUp).Row
'copy header row
.Row(1).Copy Destination:=Sheets("Sheet2").Rows(1)
For RowCount = 2 To LastRow
If InStr(.Range("C" & RowCount), " ") = 0 Then
.Row(RowCount).Copy _
Destination:=Sheets("Sheet2").Rows(NewRowCount)
NewRowCount = NewRowCount + 1
End If
Next RowCount
End With

End Sub
Sub extractmultinames()

NewRowCount = 2
With ActiveSheet
LastRow = .Range("C" & Rows.Count).End(xlUp).Row
'copy header row
.Row(1).Copy Destination:=Sheets("Sheet2").Rows(1)
For RowCount = 2 To LastRow
If InStr(.Range("C" & RowCount), " ") 0 Then
.Row(RowCount).Copy _
Destination:=Sheets("Sheet2").Rows(NewRowCount)
NewRowCount = NewRowCount + 1
End If
Next RowCount
End With

"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 does either have only one name or more
than one name.
Any ideas in terms of a macro or function?

Regards
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 54
Default extracting specific rows
Joel
i pasted the code into a VB module, press F5 and then get the message:
"run time error "438"
Object does not support this property or method"


"Joel" wrote:

Sub extractsinglenames()

NewRowCount = 2
With ActiveSheet
LastRow = .Range("C" & Rows.Count).End(xlUp).Row
'copy header row
.Row(1).Copy Destination:=Sheets("Sheet2").Rows(1)
For RowCount = 2 To LastRow
If InStr(.Range("C" & RowCount), " ") = 0 Then
.Row(RowCount).Copy _
Destination:=Sheets("Sheet2").Rows(NewRowCount)
NewRowCount = NewRowCount + 1
End If
Next RowCount
End With

End Sub
Sub extractmultinames()

NewRowCount = 2
With ActiveSheet
LastRow = .Range("C" & Rows.Count).End(xlUp).Row
'copy header row
.Row(1).Copy Destination:=Sheets("Sheet2").Rows(1)
For RowCount = 2 To LastRow
If InStr(.Range("C" & RowCount), " ") 0 Then
.Row(RowCount).Copy _
Destination:=Sheets("Sheet2").Rows(NewRowCount)
NewRowCount = NewRowCount + 1
End If
Next RowCount
End With

"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 does either have only one name or more
than one name.
Any ideas in terms of a macro or function?

Regards
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 9,101
Default extracting specific rows
Sorry, in a couple o spots I typed Row (no s) instead of Rows.

Sub extractsinglenames()

NewRowCount = 2
With ActiveSheet
LastRow = .Range("C" & Rows.Count).End(xlUp).Row
'copy header row
.Rows(1).Copy Destination:=Sheets("Sheet2").Rows(1)
For RowCount = 2 To LastRow
If InStr(.Range("C" & RowCount), " ") = 0 Then
.Rows(RowCount).Copy _
Destination:=Sheets("Sheet2").Rows(NewRowCount)
NewRowCount = NewRowCount + 1
End If
Next RowCount
End With

End Sub
Sub extractmultinames()

NewRowCount = 2
With ActiveSheet
LastRow = .Range("C" & Rows.Count).End(xlUp).Row
'copy header row
.Rows(1).Copy Destination:=Sheets("Sheet2").Rows(1)
For RowCount = 2 To LastRow
If InStr(.Range("C" & RowCount), " ") 0 Then
.Row(sRowCount).Copy _
Destination:=Sheets("Sheet2").Rows(NewRowCount)
NewRowCount = NewRowCount + 1
End If
Next RowCount
End With


"wynand" wrote:

Joel
i pasted the code into a VB module, press F5 and then get the message:
"run time error "438"
Object does not support this property or method"


"Joel" wrote:

Sub extractsinglenames()

NewRowCount = 2
With ActiveSheet
LastRow = .Range("C" & Rows.Count).End(xlUp).Row
'copy header row
.Row(1).Copy Destination:=Sheets("Sheet2").Rows(1)
For RowCount = 2 To LastRow
If InStr(.Range("C" & RowCount), " ") = 0 Then
.Row(RowCount).Copy _
Destination:=Sheets("Sheet2").Rows(NewRowCount)
NewRowCount = NewRowCount + 1
End If
Next RowCount
End With

End Sub
Sub extractmultinames()

NewRowCount = 2
With ActiveSheet
LastRow = .Range("C" & Rows.Count).End(xlUp).Row
'copy header row
.Row(1).Copy Destination:=Sheets("Sheet2").Rows(1)
For RowCount = 2 To LastRow
If InStr(.Range("C" & RowCount), " ") 0 Then
.Row(RowCount).Copy _
Destination:=Sheets("Sheet2").Rows(NewRowCount)
NewRowCount = NewRowCount + 1
End If
Next RowCount
End With

"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 does either have only one name or more
than one name.
Any ideas in terms of a macro or function?

Regards
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 54
Default extracting specific rows
Joel
Thank you single names is working excellent.
Multi names shows the following debug error:
.Row(sRowCount).Copy _
Destination:=Sheets("Sheet2").Rows(NewRowCount)'

"Joel" wrote:

Sorry, in a couple o spots I typed Row (no s) instead of Rows.

Sub extractsinglenames()

NewRowCount = 2
With ActiveSheet
LastRow = .Range("C" & Rows.Count).End(xlUp).Row
'copy header row
.Rows(1).Copy Destination:=Sheets("Sheet2").Rows(1)
For RowCount = 2 To LastRow
If InStr(.Range("C" & RowCount), " ") = 0 Then
.Rows(RowCount).Copy _
Destination:=Sheets("Sheet2").Rows(NewRowCount)
NewRowCount = NewRowCount + 1
End If
Next RowCount
End With

End Sub
Sub extractmultinames()

NewRowCount = 2
With ActiveSheet
LastRow = .Range("C" & Rows.Count).End(xlUp).Row
'copy header row
.Rows(1).Copy Destination:=Sheets("Sheet2").Rows(1)
For RowCount = 2 To LastRow
If InStr(.Range("C" & RowCount), " ") 0 Then
.Row(sRowCount).Copy _
Destination:=Sheets("Sheet2").Rows(NewRowCount)
NewRowCount = NewRowCount + 1
End If
Next RowCount
End With


"wynand" wrote:

Joel
i pasted the code into a VB module, press F5 and then get the message:
"run time error "438"
Object does not support this property or method"


"Joel" wrote:

Sub extractsinglenames()

NewRowCount = 2
With ActiveSheet
LastRow = .Range("C" & Rows.Count).End(xlUp).Row
'copy header row
.Row(1).Copy Destination:=Sheets("Sheet2").Rows(1)
For RowCount = 2 To LastRow
If InStr(.Range("C" & RowCount), " ") = 0 Then
.Row(RowCount).Copy _
Destination:=Sheets("Sheet2").Rows(NewRowCount)
NewRowCount = NewRowCount + 1
End If
Next RowCount
End With

End Sub
Sub extractmultinames()

NewRowCount = 2
With ActiveSheet
LastRow = .Range("C" & Rows.Count).End(xlUp).Row
'copy header row
.Row(1).Copy Destination:=Sheets("Sheet2").Rows(1)
For RowCount = 2 To LastRow
If InStr(.Range("C" & RowCount), " ") 0 Then
.Row(RowCount).Copy _
Destination:=Sheets("Sheet2").Rows(NewRowCount)
NewRowCount = NewRowCount + 1
End If
Next RowCount
End With

"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 does either have only one name or more
than one name.
Any ideas in terms of a macro or function?

Regards
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 9,101
Default extracting specific rows
I put the S in the wrong place

from
.Row(sRowCount).Copy _
Destination:=Sheets("Sheet2").Rows(NewRowCount)'
to
.Rows(RowCount).Copy _
Destination:=Sheets("Sheet2").Rows(NewRowCount)'

The two macros are the same except for on e line of code

If InStr(.Range("C" & RowCount), " ") = 0 Then

and

If InStr(.Range("C" & RowCount), " ") 0 Then


"wynand" wrote:

Joel
Thank you single names is working excellent.
Multi names shows the following debug error:
.Row(sRowCount).Copy _
Destination:=Sheets("Sheet2").Rows(NewRowCount)'

"Joel" wrote:

Sorry, in a couple o spots I typed Row (no s) instead of Rows.

Sub extractsinglenames()

NewRowCount = 2
With ActiveSheet
LastRow = .Range("C" & Rows.Count).End(xlUp).Row
'copy header row
.Rows(1).Copy Destination:=Sheets("Sheet2").Rows(1)
For RowCount = 2 To LastRow
If InStr(.Range("C" & RowCount), " ") = 0 Then
.Rows(RowCount).Copy _
Destination:=Sheets("Sheet2").Rows(NewRowCount)
NewRowCount = NewRowCount + 1
End If
Next RowCount
End With

End Sub
Sub extractmultinames()

NewRowCount = 2
With ActiveSheet
LastRow = .Range("C" & Rows.Count).End(xlUp).Row
'copy header row
.Rows(1).Copy Destination:=Sheets("Sheet2").Rows(1)
For RowCount = 2 To LastRow
If InStr(.Range("C" & RowCount), " ") 0 Then
.Row(sRowCount).Copy _
Destination:=Sheets("Sheet2").Rows(NewRowCount)
NewRowCount = NewRowCount + 1
End If
Next RowCount
End With


"wynand" wrote:

Joel
i pasted the code into a VB module, press F5 and then get the message:
"run time error "438"
Object does not support this property or method"


"Joel" wrote:

Sub extractsinglenames()

NewRowCount = 2
With ActiveSheet
LastRow = .Range("C" & Rows.Count).End(xlUp).Row
'copy header row
.Row(1).Copy Destination:=Sheets("Sheet2").Rows(1)
For RowCount = 2 To LastRow
If InStr(.Range("C" & RowCount), " ") = 0 Then
.Row(RowCount).Copy _
Destination:=Sheets("Sheet2").Rows(NewRowCount)
NewRowCount = NewRowCount + 1
End If
Next RowCount
End With

End Sub
Sub extractmultinames()

NewRowCount = 2
With ActiveSheet
LastRow = .Range("C" & Rows.Count).End(xlUp).Row
'copy header row
.Row(1).Copy Destination:=Sheets("Sheet2").Rows(1)
For RowCount = 2 To LastRow
If InStr(.Range("C" & RowCount), " ") 0 Then
.Row(RowCount).Copy _
Destination:=Sheets("Sheet2").Rows(NewRowCount)
NewRowCount = NewRowCount + 1
End If
Next RowCount
End With

"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 does either have only one name or more
than one name.
Any ideas in terms of a macro or function?

Regards
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 54
Default extracting specific rows
thanks works great!!

"Joel" wrote:

I put the S in the wrong place

from
.Row(sRowCount).Copy _
Destination:=Sheets("Sheet2").Rows(NewRowCount)'
to
.Rows(RowCount).Copy _
Destination:=Sheets("Sheet2").Rows(NewRowCount)'

The two macros are the same except for on e line of code

If InStr(.Range("C" & RowCount), " ") = 0 Then

and

If InStr(.Range("C" & RowCount), " ") 0 Then


"wynand" wrote:

Joel
Thank you single names is working excellent.
Multi names shows the following debug error:
.Row(sRowCount).Copy _
Destination:=Sheets("Sheet2").Rows(NewRowCount)'

"Joel" wrote:

Sorry, in a couple o spots I typed Row (no s) instead of Rows.

Sub extractsinglenames()

NewRowCount = 2
With ActiveSheet
LastRow = .Range("C" & Rows.Count).End(xlUp).Row
'copy header row
.Rows(1).Copy Destination:=Sheets("Sheet2").Rows(1)
For RowCount = 2 To LastRow
If InStr(.Range("C" & RowCount), " ") = 0 Then
.Rows(RowCount).Copy _
Destination:=Sheets("Sheet2").Rows(NewRowCount)
NewRowCount = NewRowCount + 1
End If
Next RowCount
End With

End Sub
Sub extractmultinames()

NewRowCount = 2
With ActiveSheet
LastRow = .Range("C" & Rows.Count).End(xlUp).Row
'copy header row
.Rows(1).Copy Destination:=Sheets("Sheet2").Rows(1)
For RowCount = 2 To LastRow
If InStr(.Range("C" & RowCount), " ") 0 Then
.Row(sRowCount).Copy _
Destination:=Sheets("Sheet2").Rows(NewRowCount)
NewRowCount = NewRowCount + 1
End If
Next RowCount
End With


"wynand" wrote:

Joel
i pasted the code into a VB module, press F5 and then get the message:
"run time error "438"
Object does not support this property or method"


"Joel" wrote:

Sub extractsinglenames()

NewRowCount = 2
With ActiveSheet
LastRow = .Range("C" & Rows.Count).End(xlUp).Row
'copy header row
.Row(1).Copy Destination:=Sheets("Sheet2").Rows(1)
For RowCount = 2 To LastRow
If InStr(.Range("C" & RowCount), " ") = 0 Then
.Row(RowCount).Copy _
Destination:=Sheets("Sheet2").Rows(NewRowCount)
NewRowCount = NewRowCount + 1
End If
Next RowCount
End With

End Sub
Sub extractmultinames()

NewRowCount = 2
With ActiveSheet
LastRow = .Range("C" & Rows.Count).End(xlUp).Row
'copy header row
.Row(1).Copy Destination:=Sheets("Sheet2").Rows(1)
For RowCount = 2 To LastRow
If InStr(.Range("C" & RowCount), " ") 0 Then
.Row(RowCount).Copy _
Destination:=Sheets("Sheet2").Rows(NewRowCount)
NewRowCount = NewRowCount + 1
End If
Next RowCount
End With

"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 does either have only one name or more
than one name.
Any ideas in terms of a macro or function?

Regards
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Extracting characters before or after a specific character Brian Excel Worksheet Functions 4 April 27th 23 07:42 PM
extracting specific values and replacing in set column hudini Excel Discussion (Misc queries) 5 September 3rd 07 05:16 PM
Extracting specific word in a cell Conditional Formatting Excel Worksheet Functions 2 February 14th 06 09:57 PM
Extracting specific data from a cell Neil Bowen Excel Worksheet Functions 5 October 30th 05 04:33 PM
Pivot Table - Extracting specific data JT Excel Worksheet Functions 1 June 3rd 05 06:05 PM


All times are GMT +1. The time now is 05:28 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement