| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting rows in two columns with a code in visual basic
Hello,
I have a problem with making a code in visual basic. I would like to make a code that deletes all rows in which the value of column G starts with a W (after W there is a number, example: W-123-231). I can do this, but now comes the most difficult thing. All my rows have numbers in column C, varying from 100 till 1000 (my total file is about 5000 rows). Some rows have the same number in column C (because they are related to each other: same kind of product). Because I would like to delete all rows for which the first value in column G is a W, I also would like to delete all rows that are related to the rows with a W in column G. So for the row which value in column G starts with a W, all related rows should be deleted as well. This means that all rows with the same number in column C as the row with a W in the G column, have to be deleted. So, in summary, all rows that start with a `Wยด in column G and the related rows (which have the same number in column C) have to be deleted. I dont know how I can put this in a code in visual basic, so it would be great if someone could tell me! I hope my problem is clear, if it is not let me know. Thank you very much for helping me! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Deleting rows in two columns with a code in visual basic
The best way of doing this is with a Sumproductt worksheet formula in an
auxilary column. I would first get it right using manually entered formulas and then convert to a macro the line below if put in Row D in an auxilary column wil put 1 if C10 matched the number in Column A and a W proceeded the number =Sumproduct(--(left(A1:A100,1)="W"),--(mid(A1:A100,2)=C10)) You need an OR statment to get your complete solution =Sumproduct(--(left(A1:A100,1)="W")+Sumproduct(--(left(A1:A100,1)="W"),--(mid(A1:A100,2)=C10)) The VBa for this will be something like this A_RANGE = "A1:A100" for RowCount = 1 to 100 Range("X" & RowCount).formula = "=Sumproduct(--(left(" & A_RANGE & _ ",1)=""W"")+Sumproduct(--(left(" & A_RANGE & ",1)=""W"")," & _ "--(mid(" & A_RANGE & ",2)=C" & RowCount & "))" next RowCount "Ruben" wrote: Hello, I have a problem with making a code in visual basic. I would like to make a code that deletes all rows in which the value of column G starts with a W (after W there is a number, example: W-123-231). I can do this, but now comes the most difficult thing. All my rows have numbers in column C, varying from 100 till 1000 (my total file is about 5000 rows). Some rows have the same number in column C (because they are related to each other: same kind of product). Because I would like to delete all rows for which the first value in column G is a W, I also would like to delete all rows that are related to the rows with a W in column G. So for the row which value in column G starts with a W, all related rows should be deleted as well. This means that all rows with the same number in column C as the row with a W in the G column, have to be deleted. So, in summary, all rows that start with a `Wยด in column G and the related rows (which have the same number in column C) have to be deleted. I dont know how I can put this in a code in visual basic, so it would be great if someone could tell me! I hope my problem is clear, if it is not let me know. Thank you very much for helping me! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Deleting rows in two columns with a code in visual basic
Here is the logic I think will take least amount of time;
Sort on Column C Pick the first value in Column C Loop till value in C changes Check whether Column G starts with a W if Yes then Mark all rows with same C for deletion from beginning till C changes Let me know if you want the code also. You can send the file to me at . I will put in the code and send to you within 24 hours. "Ruben" wrote: Hello, I have a problem with making a code in visual basic. I would like to make a code that deletes all rows in which the value of column G starts with a W (after W there is a number, example: W-123-231). I can do this, but now comes the most difficult thing. All my rows have numbers in column C, varying from 100 till 1000 (my total file is about 5000 rows). Some rows have the same number in column C (because they are related to each other: same kind of product). Because I would like to delete all rows for which the first value in column G is a W, I also would like to delete all rows that are related to the rows with a W in column G. So for the row which value in column G starts with a W, all related rows should be deleted as well. This means that all rows with the same number in column C as the row with a W in the G column, have to be deleted. So, in summary, all rows that start with a `Wยด in column G and the related rows (which have the same number in column C) have to be deleted. I dont know how I can put this in a code in visual basic, so it would be great if someone could tell me! I hope my problem is clear, if it is not let me know. Thank you very much for helping me! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Deleting rows in two columns with a code in visual basic
A re-read suggests that you either need to have a unique list or use a macro to create a unique list. Then a for each loop for each item in the list to look for duplicates that start with w. Doable -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... try thisselect the title row(row1) datafilterautofiltercustombegins with w abbbbb ddd wass ddd wass ddd wass ddd wass ddd wass ddd wass ddd wass -- Don Guillett Microsoft MVP Excel SalesAid Software "Ruben" wrote in message ... Hello, I have a problem with making a code in visual basic. I would like to make a code that deletes all rows in which the value of column G starts with a W (after W there is a number, example: W-123-231). I can do this, but now comes the most difficult thing. All my rows have numbers in column C, varying from 100 till 1000 (my total file is about 5000 rows). Some rows have the same number in column C (because they are related to each other: same kind of product). Because I would like to delete all rows for which the first value in column G is a W, I also would like to delete all rows that are related to the rows with a W in column G. So for the row which value in column G starts with a W, all related rows should be deleted as well. This means that all rows with the same number in column C as the row with a W in the G column, have to be deleted. So, in summary, all rows that start with a `Wยด in column G and the related rows (which have the same number in column C) have to be deleted. I dont know how I can put this in a code in visual basic, so it would be great if someone could tell me! I hope my problem is clear, if it is not let me know. Thank you very much for helping me! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Deleting rows in two columns with a code in visual basic
It works now, so thanks for all your helpful reactions!
Ruben "Sheeloo" wrote: Here is the logic I think will take least amount of time; Sort on Column C Pick the first value in Column C Loop till value in C changes Check whether Column G starts with a W if Yes then Mark all rows with same C for deletion from beginning till C changes Let me know if you want the code also. You can send the file to me at . I will put in the code and send to you within 24 hours. "Ruben" wrote: Hello, I have a problem with making a code in visual basic. I would like to make a code that deletes all rows in which the value of column G starts with a W (after W there is a number, example: W-123-231). I can do this, but now comes the most difficult thing. All my rows have numbers in column C, varying from 100 till 1000 (my total file is about 5000 rows). Some rows have the same number in column C (because they are related to each other: same kind of product). Because I would like to delete all rows for which the first value in column G is a W, I also would like to delete all rows that are related to the rows with a W in column G. So for the row which value in column G starts with a W, all related rows should be deleted as well. This means that all rows with the same number in column C as the row with a W in the G column, have to be deleted. So, in summary, all rows that start with a `Wยด in column G and the related rows (which have the same number in column C) have to be deleted. I dont know how I can put this in a code in visual basic, so it would be great if someone could tell me! I hope my problem is clear, if it is not let me know. Thank you very much for helping me! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Data filter and deleting rows in visual basic | Excel Discussion (Misc queries) | |||
| I need a visual basic code....please | Excel Discussion (Misc queries) | |||
| Deleting pictures in Excel using visual basic | New Users to Excel | |||
| Visual Basic code available for all open workbooks | Excel Worksheet Functions | |||
| Visual Basic Code Remains in "memory" | Excel Worksheet Functions | |||
Linear Mode
