DaNegotiator
New Member
- Joined
- Aug 27, 2013
- Messages
- 1
Greetings all. Now, I will say that I am very new to Excel VBA and I have only learned what I have seen in online forums and self help guides. So, I apologize if this is very basic, missing proper coding etiquette, and/or if I need to have a certain proficiency to write my desired macro. With that being said...
I have a very large data set with multiple rows and columns of data. I am trying to "clean" the data and only include the data I need. I tried writing 2 macros that would 1) Delete rows based on specified criteria and 2) another macro to delete rows if the sequence of numbers are not met in the column Unfortunately, I didn't even get the first one to work.
What I tried doing was this: I put the "Delete Criteria" I wanted to use in another sheet in the 1st column (A) and then I tried to write the macro to look at "Save Criteria" and then it would look at the main sheet that contains all the data. Then it would look at the cells in a specific column. If that cell contained ANY of the values in the "Save Criteria" then it could KEEP it. If it did not have ANY of the criteria, it would DELETE the ENTIRE row that the cell is located. I tried to do the following:
Private Sub CommandButton1_Click()
row_number = 1
Do
DoEvents
row_number = row_number + 1
unitid_data = Sheet1.Range("A" & row_number)
If unitid_data = 0 Then
Sheet2.Range("A" & row_number).Delete
row_number = row_number - 1
End If
Loop Until unitid_data = ""
MsgBox "Completed"
End Sub
Well, that didn't do anything. I tried changing the Sheet name of the main data to "Sheet2" thinking that would fix it, but nothing happened. So, I know I have done multiple things wrong, I am just not sure what. Therefore, I will use a random data set to try and explain what I would like to do since my data set has over 930 columns and over 74000 rows. So here is a sample data set:
<tbody>
</tbody>
Though this data set is incredibly small in comparison, the principle is the same. I would like to have the first macro to look at the "Save Criteria" in Sheet 2 and then search the contents of each cell in the "setting" column in Sheet 1. If the cell matches any of the criteria, then it will keep it and search the next cell. If it does not have the criteria, then it would delete the ENTIRE row. So in this example, the only rows that should be left is Brazil, ElSalvador, Guatemala, and Paraguay.
Then I would like to write another that would look in a specific column (for this example I will say "effort") and if it doesn't follow an indicated pattern, then it will delete all rows that do not follow the pattern. The pattern would have to be "0, 1, 2" in the effort column. If it sees that pattern as indicated in the second sheet, then it will keep the row. If it does not, it will keep the entire contents. So, in this example, the rows that would be kept are Bolivia, Brazil, Chile, Cuba, DominicanRep, Ecuador, Haiti, Honduras, and Jamaica.
Now, I will reiterate that I have over 930 columns and 7500 rows. Therefore, any help that you could provide would be extremely helpful. Thank you in advance for your comments, suggestions, and help.
I have a very large data set with multiple rows and columns of data. I am trying to "clean" the data and only include the data I need. I tried writing 2 macros that would 1) Delete rows based on specified criteria and 2) another macro to delete rows if the sequence of numbers are not met in the column Unfortunately, I didn't even get the first one to work.
What I tried doing was this: I put the "Delete Criteria" I wanted to use in another sheet in the 1st column (A) and then I tried to write the macro to look at "Save Criteria" and then it would look at the main sheet that contains all the data. Then it would look at the cells in a specific column. If that cell contained ANY of the values in the "Save Criteria" then it could KEEP it. If it did not have ANY of the criteria, it would DELETE the ENTIRE row that the cell is located. I tried to do the following:
Private Sub CommandButton1_Click()
row_number = 1
Do
DoEvents
row_number = row_number + 1
unitid_data = Sheet1.Range("A" & row_number)
If unitid_data = 0 Then
Sheet2.Range("A" & row_number).Delete
row_number = row_number - 1
End If
Loop Until unitid_data = ""
MsgBox "Completed"
End Sub
Well, that didn't do anything. I tried changing the Sheet name of the main data to "Sheet2" thinking that would fix it, but nothing happened. So, I know I have done multiple things wrong, I am just not sure what. Therefore, I will use a random data set to try and explain what I would like to do since my data set has over 930 columns and over 74000 rows. So here is a sample data set:
Sheet 1 | Sheet 2 Macro 1 | Sheet 2 Macro 2 | ||||
setting | effort | change | Save Criteria(1) | Save Criteria(2) | ||
Bolivia | 46 | 0 | 1 | 74 | 0 | |
Brazil | 74 | 1 | 10 | 55 | 1 | |
Chile | 89 | 2 | 29 | 60 | 2 | |
Colombia | 77 | 0 | 25 | |||
CostaRica | 84 | 1 | 29 | |||
Cuba | 89 | 0 | 40 | |||
DominicanRep | 68 | 1 | 21 | |||
Ecuador | 70 | 2 | 0 | |||
ElSalvador | 60 | 0 | 13 | |||
Guatemala | 55 | 1 | 4 | |||
Haiti | 35 | 0 | 0 | |||
Honduras | 51 | 1 | 7 | |||
Jamaica | 87 | 2 | 21 | |||
Mexico | 83 | 0 | 9 | |||
Nicaragua | 68 | 0 | 7 | |||
Panama | 84 | 1 | 22 | |||
Paraguay | 74 | 1 | 6 | |||
Peru | 73 | 0 | 2 | |||
TrinidadTobago | 84 | 2 | 29 | |||
Venezuela | 91 | 1 | 11 |
<tbody>
</tbody>
Though this data set is incredibly small in comparison, the principle is the same. I would like to have the first macro to look at the "Save Criteria" in Sheet 2 and then search the contents of each cell in the "setting" column in Sheet 1. If the cell matches any of the criteria, then it will keep it and search the next cell. If it does not have the criteria, then it would delete the ENTIRE row. So in this example, the only rows that should be left is Brazil, ElSalvador, Guatemala, and Paraguay.
Then I would like to write another that would look in a specific column (for this example I will say "effort") and if it doesn't follow an indicated pattern, then it will delete all rows that do not follow the pattern. The pattern would have to be "0, 1, 2" in the effort column. If it sees that pattern as indicated in the second sheet, then it will keep the row. If it does not, it will keep the entire contents. So, in this example, the rows that would be kept are Bolivia, Brazil, Chile, Cuba, DominicanRep, Ecuador, Haiti, Honduras, and Jamaica.
Now, I will reiterate that I have over 930 columns and 7500 rows. Therefore, any help that you could provide would be extremely helpful. Thank you in advance for your comments, suggestions, and help.