Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Locating a value anywhere in a row and copying that row to new she
I have a multi-column worksheet that I need to search for a particular
string, which could appear in any column (and possibly in more than one column) in a row. When I find that string, I want to copy the entire row to the next empty row on a separate worksheet. However, if the value appears more than once in a row, I only want one copy. I have a number of have-way-there solutions but nothing is doing the job properly. Has anyone any suggestions please? Many thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Locating a value anywhere in a row and copying that row to new she
Sue-
If you focus on rows that contain the string rather than columns, the problem becomes easier to deal with. You esentially are looking for any row that has one or more instances of your string. If you set up your procedure loop through the data to seach for the string on one row at a time, you can quickly ascertain whether the row needs to be copied or not. If it contains the string, copy it and move to the next row. If it doesn't contain the string, simply move on to the next row. I hope this helps Stan Shoemaker Palo Alto, CA "SueJB" wrote: I have a multi-column worksheet that I need to search for a particular string, which could appear in any column (and possibly in more than one column) in a row. When I find that string, I want to copy the entire row to the next empty row on a separate worksheet. However, if the value appears more than once in a row, I only want one copy. I have a number of have-way-there solutions but nothing is doing the job properly. Has anyone any suggestions please? Many thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Locating a value anywhere in a row and copying that row to new
Hi Stan
Thanks for this - in fact, I have got this loop working and it will identify any row that contains the string, in any of that row's columns. Unfortunately it doesn't do much else! Do you have any suggestions on how to pass the identifier for that row to a copy routine? Sue "stanshoe" wrote: Sue- If you focus on rows that contain the string rather than columns, the problem becomes easier to deal with. You esentially are looking for any row that has one or more instances of your string. If you set up your procedure loop through the data to seach for the string on one row at a time, you can quickly ascertain whether the row needs to be copied or not. If it contains the string, copy it and move to the next row. If it doesn't contain the string, simply move on to the next row. I hope this helps Stan Shoemaker Palo Alto, CA "SueJB" wrote: I have a multi-column worksheet that I need to search for a particular string, which could appear in any column (and possibly in more than one column) in a row. When I find that string, I want to copy the entire row to the next empty row on a separate worksheet. However, if the value appears more than once in a row, I only want one copy. I have a number of have-way-there solutions but nothing is doing the job properly. Has anyone any suggestions please? Many thanks! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Locating a value anywhere in a row and copying that row to new
Once you have identifed the row, you can use the copy method to copy it.
Exactly what you want to copy (Rows(r).Copy, Activecell.EntireRow.Copy, etc.) depends on how you are looping through the rows. Here is some code looks for a "String" in the rows selected before the procedure is run. The macro assumes that the data to be tested is on the sheet "SourceSheet" and that you want to move it to the sheet "DestinationSheet". Sub find_copyRows() 'this procedure assumes the "SourceSheet" is the active worksheet and 'the cells (rows) to be searched have been selected before the macro is run. Dim firstrow As Integer Dim lastrow As Integer firstrow = ActiveCell.Row lastrow = firstrow + Selection.Rows.count - 1 For r = firstrow To lastrow Set c = Rows(r).Find("String") If Not c Is Nothing Then Rows(r).Copy Sheets("DestinationSheet").Activate Range("A65000").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll) Sheets("SourceSheet").Activate End If Next r End Sub Stan Shoemaker Palo Alto, CA End Sub "SueJB" wrote: Hi Stan Thanks for this - in fact, I have got this loop working and it will identify any row that contains the string, in any of that row's columns. Unfortunately it doesn't do much else! Do you have any suggestions on how to pass the identifier for that row to a copy routine? Sue "stanshoe" wrote: Sue- If you focus on rows that contain the string rather than columns, the problem becomes easier to deal with. You esentially are looking for any row that has one or more instances of your string. If you set up your procedure loop through the data to seach for the string on one row at a time, you can quickly ascertain whether the row needs to be copied or not. If it contains the string, copy it and move to the next row. If it doesn't contain the string, simply move on to the next row. I hope this helps Stan Shoemaker Palo Alto, CA "SueJB" wrote: I have a multi-column worksheet that I need to search for a particular string, which could appear in any column (and possibly in more than one column) in a row. When I find that string, I want to copy the entire row to the next empty row on a separate worksheet. However, if the value appears more than once in a row, I only want one copy. I have a number of have-way-there solutions but nothing is doing the job properly. Has anyone any suggestions please? Many thanks! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Locating a value anywhere in a row and copying that row to new
Hello again Stan
Many thanks for this, it's really helpful and does what I hoped. If you don't mind, can you - or anyone else - help with another problem on this? I would like this sub to work without the user selecting the range manually (ie using the UsedRange property of the active sheet). I have the following code: firstRow = ActiveSheet.UsedRange.Cells(1).row lastRow = ActiveSheet.UsedRange.Rows.Count + firstRow - 1 but it returns a Runtime Error '6' - overflow - which seems to refer to the second line of the code. Any pointers most welcomed - as you may have guessed, I'm very new to VBA programming! Sue "stanshoe" wrote: Once you have identifed the row, you can use the copy method to copy it. Exactly what you want to copy (Rows(r).Copy, Activecell.EntireRow.Copy, etc.) depends on how you are looping through the rows. Here is some code looks for a "String" in the rows selected before the procedure is run. The macro assumes that the data to be tested is on the sheet "SourceSheet" and that you want to move it to the sheet "DestinationSheet". Sub find_copyRows() 'this procedure assumes the "SourceSheet" is the active worksheet and 'the cells (rows) to be searched have been selected before the macro is run. Dim firstrow As Integer Dim lastrow As Integer firstrow = ActiveCell.Row lastrow = firstrow + Selection.Rows.count - 1 For r = firstrow To lastrow Set c = Rows(r).Find("String") If Not c Is Nothing Then Rows(r).Copy Sheets("DestinationSheet").Activate Range("A65000").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll) Sheets("SourceSheet").Activate End If Next r End Sub Stan Shoemaker Palo Alto, CA End Sub "SueJB" wrote: Hi Stan Thanks for this - in fact, I have got this loop working and it will identify any row that contains the string, in any of that row's columns. Unfortunately it doesn't do much else! Do you have any suggestions on how to pass the identifier for that row to a copy routine? Sue "stanshoe" wrote: Sue- If you focus on rows that contain the string rather than columns, the problem becomes easier to deal with. You esentially are looking for any row that has one or more instances of your string. If you set up your procedure loop through the data to seach for the string on one row at a time, you can quickly ascertain whether the row needs to be copied or not. If it contains the string, copy it and move to the next row. If it doesn't contain the string, simply move on to the next row. I hope this helps Stan Shoemaker Palo Alto, CA "SueJB" wrote: I have a multi-column worksheet that I need to search for a particular string, which could appear in any column (and possibly in more than one column) in a row. When I find that string, I want to copy the entire row to the next empty row on a separate worksheet. However, if the value appears more than once in a row, I only want one copy. I have a number of have-way-there solutions but nothing is doing the job properly. Has anyone any suggestions please? Many thanks! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Locating a value anywhere in a row and copying that row to new
Sue-
There is another property called "CurrentRegion" that I find most useful if the data set has no enitirely blank rows or columns in the data set. CurrentRegion is the range bounded by any combination of blank rows and blank columns. If you data is in rows 1 to 100 starting in Column A and row 51 is blank, Range("A1").CurrentRegion.Select will return a range of cells from Row 1 to Row 50. If Row 51 is not blank, you will get all of the rows between 1 and 100. Assuming your data starts in Cell A1, you could use the following code to automatically select your range: Range("A1").CurrentRegion.Select firstRow = ActiveCell.row lastRow = firstRow + Selection.Rows.count - 1 Alternatively, if you data allways starts in row 1, but there are blank rows in the middle, you could establish the last row by selecting a cell at the bottom of the worksheet and using the "End(xlUp)" up method to locate the last row. In this situation you could use code like: firstRow = 1 lastRow = Range("A60000").End(xlUp).Row Stan Shoemaker Palo Alto, CA "SueJB" wrote: Hello again Stan Many thanks for this, it's really helpful and does what I hoped. If you don't mind, can you - or anyone else - help with another problem on this? I would like this sub to work without the user selecting the range manually (ie using the UsedRange property of the active sheet). I have the following code: firstRow = ActiveSheet.UsedRange.Cells(1).row lastRow = ActiveSheet.UsedRange.Rows.Count + firstRow - 1 but it returns a Runtime Error '6' - overflow - which seems to refer to the second line of the code. Any pointers most welcomed - as you may have guessed, I'm very new to VBA programming! Sue "stanshoe" wrote: Once you have identifed the row, you can use the copy method to copy it. Exactly what you want to copy (Rows(r).Copy, Activecell.EntireRow.Copy, etc.) depends on how you are looping through the rows. Here is some code looks for a "String" in the rows selected before the procedure is run. The macro assumes that the data to be tested is on the sheet "SourceSheet" and that you want to move it to the sheet "DestinationSheet". Sub find_copyRows() 'this procedure assumes the "SourceSheet" is the active worksheet and 'the cells (rows) to be searched have been selected before the macro is run. Dim firstrow As Integer Dim lastrow As Integer firstrow = ActiveCell.Row lastrow = firstrow + Selection.Rows.count - 1 For r = firstrow To lastrow Set c = Rows(r).Find("String") If Not c Is Nothing Then Rows(r).Copy Sheets("DestinationSheet").Activate Range("A65000").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll) Sheets("SourceSheet").Activate End If Next r End Sub Stan Shoemaker Palo Alto, CA End Sub "SueJB" wrote: Hi Stan Thanks for this - in fact, I have got this loop working and it will identify any row that contains the string, in any of that row's columns. Unfortunately it doesn't do much else! Do you have any suggestions on how to pass the identifier for that row to a copy routine? Sue "stanshoe" wrote: Sue- If you focus on rows that contain the string rather than columns, the problem becomes easier to deal with. You esentially are looking for any row that has one or more instances of your string. If you set up your procedure loop through the data to seach for the string on one row at a time, you can quickly ascertain whether the row needs to be copied or not. If it contains the string, copy it and move to the next row. If it doesn't contain the string, simply move on to the next row. I hope this helps Stan Shoemaker Palo Alto, CA "SueJB" wrote: I have a multi-column worksheet that I need to search for a particular string, which could appear in any column (and possibly in more than one column) in a row. When I find that string, I want to copy the entire row to the next empty row on a separate worksheet. However, if the value appears more than once in a row, I only want one copy. I have a number of have-way-there solutions but nothing is doing the job properly. Has anyone any suggestions please? Many thanks! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Locating a value anywhere in a row and copying that row to new
Stan
Very many thanks - that should solve my problem beautifully and has taught me a lot too. Best wishes Sue "stanshoe" wrote: Sue- There is another property called "CurrentRegion" that I find most useful if the data set has no enitirely blank rows or columns in the data set. CurrentRegion is the range bounded by any combination of blank rows and blank columns. If you data is in rows 1 to 100 starting in Column A and row 51 is blank, Range("A1").CurrentRegion.Select will return a range of cells from Row 1 to Row 50. If Row 51 is not blank, you will get all of the rows between 1 and 100. Assuming your data starts in Cell A1, you could use the following code to automatically select your range: Range("A1").CurrentRegion.Select firstRow = ActiveCell.row lastRow = firstRow + Selection.Rows.count - 1 Alternatively, if you data allways starts in row 1, but there are blank rows in the middle, you could establish the last row by selecting a cell at the bottom of the worksheet and using the "End(xlUp)" up method to locate the last row. In this situation you could use code like: firstRow = 1 lastRow = Range("A60000").End(xlUp).Row Stan Shoemaker Palo Alto, CA "SueJB" wrote: Hello again Stan Many thanks for this, it's really helpful and does what I hoped. If you don't mind, can you - or anyone else - help with another problem on this? I would like this sub to work without the user selecting the range manually (ie using the UsedRange property of the active sheet). I have the following code: firstRow = ActiveSheet.UsedRange.Cells(1).row lastRow = ActiveSheet.UsedRange.Rows.Count + firstRow - 1 but it returns a Runtime Error '6' - overflow - which seems to refer to the second line of the code. Any pointers most welcomed - as you may have guessed, I'm very new to VBA programming! Sue "stanshoe" wrote: Once you have identifed the row, you can use the copy method to copy it. Exactly what you want to copy (Rows(r).Copy, Activecell.EntireRow.Copy, etc.) depends on how you are looping through the rows. Here is some code looks for a "String" in the rows selected before the procedure is run. The macro assumes that the data to be tested is on the sheet "SourceSheet" and that you want to move it to the sheet "DestinationSheet". Sub find_copyRows() 'this procedure assumes the "SourceSheet" is the active worksheet and 'the cells (rows) to be searched have been selected before the macro is run. Dim firstrow As Integer Dim lastrow As Integer firstrow = ActiveCell.Row lastrow = firstrow + Selection.Rows.count - 1 For r = firstrow To lastrow Set c = Rows(r).Find("String") If Not c Is Nothing Then Rows(r).Copy Sheets("DestinationSheet").Activate Range("A65000").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll) Sheets("SourceSheet").Activate End If Next r End Sub Stan Shoemaker Palo Alto, CA End Sub "SueJB" wrote: Hi Stan Thanks for this - in fact, I have got this loop working and it will identify any row that contains the string, in any of that row's columns. Unfortunately it doesn't do much else! Do you have any suggestions on how to pass the identifier for that row to a copy routine? Sue "stanshoe" wrote: Sue- If you focus on rows that contain the string rather than columns, the problem becomes easier to deal with. You esentially are looking for any row that has one or more instances of your string. If you set up your procedure loop through the data to seach for the string on one row at a time, you can quickly ascertain whether the row needs to be copied or not. If it contains the string, copy it and move to the next row. If it doesn't contain the string, simply move on to the next row. I hope this helps Stan Shoemaker Palo Alto, CA "SueJB" wrote: I have a multi-column worksheet that I need to search for a particular string, which could appear in any column (and possibly in more than one column) in a row. When I find that string, I want to copy the entire row to the next empty row on a separate worksheet. However, if the value appears more than once in a row, I only want one copy. I have a number of have-way-there solutions but nothing is doing the job properly. Has anyone any suggestions please? Many thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Locating Under Payments (HELP!) | Excel Discussion (Misc queries) | |||
Locating | New Users to Excel | |||
locating the top 5 number (in a col) | Excel Worksheet Functions | |||
Dynamically locating value; selecting that row and everything above (or below) and deleting/copying | Excel Programming | |||
locating charts | Excel Programming |