Excel VBA - How to remove columns where less than X% of rows have value [closed]
I know how to write formulas in Excel and I have written only a couple basic macros in Excel for formatting cell data. I just completed a rather large data export in to Excel, but I know only some of it is relevant, so I want to write a macro that will look through every column (1500+), and for each column, check to see if at least X% of the rows (100k+) have a value (and one that is not a string that equals "null".
I honestly don't even know where to start. Any HELP is appreciated.
excel vba excel-vba
asked Nov 21 '18 at 18:57
Uchiha ItachiUchiha Itachi
8541928
closed as too broad by Cindy Meister, BigBen, SJR, Storax, Comintern Nov 21 '18 at 19:51
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
|
show 3 more comments
I know how to write formulas in Excel and I have written only a couple basic macros in Excel for formatting cell data. I just completed a rather large data export in to Excel, but I know only some of it is relevant, so I want to write a macro that will look through every column (1500+), and for each column, check to see if at least X% of the rows (100k+) have a value (and one that is not a string that equals "null".
I honestly don't even know where to start. Any HELP is appreciated.
excel vba excel-vba
asked Nov 21 '18 at 18:57
Uchiha ItachiUchiha Itachi
8541928
closed as too broad by Cindy Meister, BigBen, SJR, Storax, Comintern Nov 21 '18 at 19:51
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
are you looking at the percentage of numerical values or just any cells that arent "null"
– Brotato
Nov 21 '18 at 19:13
@Brotato - some of the cells simply don't have any value in them, but some also have a string value of "null". I want to count both.
– Uchiha Itachi
Nov 21 '18 at 19:18
2
From what I've seen, people here want you to have a code to begin with, and if you're having issues with your code, they can fix it for you. So in your case, without any code at all, people think you just want others to make the code for you..
– Basher
Nov 21 '18 at 19:30
Since you don't know where to start, let's go and make one. First, we need to find the percentage that is populated. Does this mean you want to find the last row of any columns, and use that as a basis? Ex: Column A has 100,000 rows, whereas Column B has 200,000 rows. By using Column B as the "last row", would Column A's percentage be considered 50% filled?
– Basher
Nov 21 '18 at 19:35
@Basher I don't have code to start with because I don't know VBA... As to your second comment: No - I need to figure this out on a column-by-column basis. So if Column A has 100k rows, I need percentage from the 100k; same for Column B with 235K rows and so on.
– Uchiha Itachi
Nov 21 '18 at 19:39
|
show 3 more comments
I know how to write formulas in Excel and I have written only a couple basic macros in Excel for formatting cell data. I just completed a rather large data export in to Excel, but I know only some of it is relevant, so I want to write a macro that will look through every column (1500+), and for each column, check to see if at least X% of the rows (100k+) have a value (and one that is not a string that equals "null".
I honestly don't even know where to start. Any HELP is appreciated.
excel vba excel-vba
asked Nov 21 '18 at 18:57
Uchiha ItachiUchiha Itachi
8541928
I know how to write formulas in Excel and I have written only a couple basic macros in Excel for formatting cell data. I just completed a rather large data export in to Excel, but I know only some of it is relevant, so I want to write a macro that will look through every column (1500+), and for each column, check to see if at least X% of the rows (100k+) have a value (and one that is not a string that equals "null".
I honestly don't even know where to start. Any HELP is appreciated.
excel vba excel-vba
excel vba excel-vba
asked Nov 21 '18 at 18:57
Uchiha ItachiUchiha Itachi
8541928
asked Nov 21 '18 at 18:57
Uchiha ItachiUchiha Itachi
8541928
asked Nov 21 '18 at 18:57
Uchiha ItachiUchiha Itachi
8541928
asked Nov 21 '18 at 18:57
Uchiha ItachiUchiha Itachi
8541928
asked Nov 21 '18 at 18:57
Uchiha ItachiUchiha Itachi
8541928
8541928
closed as too broad by Cindy Meister, BigBen, SJR, Storax, Comintern Nov 21 '18 at 19:51
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
closed as too broad by Cindy Meister, BigBen, SJR, Storax, Comintern Nov 21 '18 at 19:51
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
are you looking at the percentage of numerical values or just any cells that arent "null"
– Brotato
Nov 21 '18 at 19:13
@Brotato - some of the cells simply don't have any value in them, but some also have a string value of "null". I want to count both.
– Uchiha Itachi
Nov 21 '18 at 19:18
2
From what I've seen, people here want you to have a code to begin with, and if you're having issues with your code, they can fix it for you. So in your case, without any code at all, people think you just want others to make the code for you..
– Basher
Nov 21 '18 at 19:30
Since you don't know where to start, let's go and make one. First, we need to find the percentage that is populated. Does this mean you want to find the last row of any columns, and use that as a basis? Ex: Column A has 100,000 rows, whereas Column B has 200,000 rows. By using Column B as the "last row", would Column A's percentage be considered 50% filled?
– Basher
Nov 21 '18 at 19:35
@Basher I don't have code to start with because I don't know VBA... As to your second comment: No - I need to figure this out on a column-by-column basis. So if Column A has 100k rows, I need percentage from the 100k; same for Column B with 235K rows and so on.
– Uchiha Itachi
Nov 21 '18 at 19:39
|
show 3 more comments
are you looking at the percentage of numerical values or just any cells that arent "null"
– Brotato
Nov 21 '18 at 19:13
@Brotato - some of the cells simply don't have any value in them, but some also have a string value of "null". I want to count both.
– Uchiha Itachi
Nov 21 '18 at 19:18
2
From what I've seen, people here want you to have a code to begin with, and if you're having issues with your code, they can fix it for you. So in your case, without any code at all, people think you just want others to make the code for you..
– Basher
Nov 21 '18 at 19:30
Since you don't know where to start, let's go and make one. First, we need to find the percentage that is populated. Does this mean you want to find the last row of any columns, and use that as a basis? Ex: Column A has 100,000 rows, whereas Column B has 200,000 rows. By using Column B as the "last row", would Column A's percentage be considered 50% filled?
– Basher
Nov 21 '18 at 19:35
@Basher I don't have code to start with because I don't know VBA... As to your second comment: No - I need to figure this out on a column-by-column basis. So if Column A has 100k rows, I need percentage from the 100k; same for Column B with 235K rows and so on.
– Uchiha Itachi
Nov 21 '18 at 19:39
are you looking at the percentage of numerical values or just any cells that arent "null"
– Brotato
Nov 21 '18 at 19:13
are you looking at the percentage of numerical values or just any cells that arent "null"
– Brotato
Nov 21 '18 at 19:13
@Brotato - some of the cells simply don't have any value in them, but some also have a string value of "null". I want to count both.
– Uchiha Itachi
Nov 21 '18 at 19:18
@Brotato - some of the cells simply don't have any value in them, but some also have a string value of "null". I want to count both.
– Uchiha Itachi
Nov 21 '18 at 19:18
2
2
From what I've seen, people here want you to have a code to begin with, and if you're having issues with your code, they can fix it for you. So in your case, without any code at all, people think you just want others to make the code for you..
– Basher
Nov 21 '18 at 19:30
From what I've seen, people here want you to have a code to begin with, and if you're having issues with your code, they can fix it for you. So in your case, without any code at all, people think you just want others to make the code for you..
– Basher
Nov 21 '18 at 19:30
Since you don't know where to start, let's go and make one. First, we need to find the percentage that is populated. Does this mean you want to find the last row of any columns, and use that as a basis? Ex: Column A has 100,000 rows, whereas Column B has 200,000 rows. By using Column B as the "last row", would Column A's percentage be considered 50% filled?
– Basher
Nov 21 '18 at 19:35
Since you don't know where to start, let's go and make one. First, we need to find the percentage that is populated. Does this mean you want to find the last row of any columns, and use that as a basis? Ex: Column A has 100,000 rows, whereas Column B has 200,000 rows. By using Column B as the "last row", would Column A's percentage be considered 50% filled?
– Basher
Nov 21 '18 at 19:35
@Basher I don't have code to start with because I don't know VBA... As to your second comment: No - I need to figure this out on a column-by-column basis. So if Column A has 100k rows, I need percentage from the 100k; same for Column B with 235K rows and so on.
– Uchiha Itachi
Nov 21 '18 at 19:39
@Basher I don't have code to start with because I don't know VBA... As to your second comment: No - I need to figure this out on a column-by-column basis. So if Column A has 100k rows, I need percentage from the 100k; same for Column B with 235K rows and so on.
– Uchiha Itachi
Nov 21 '18 at 19:39
|
show 3 more comments
2 Answers
2
active
oldest
votes
The code considers a valid range to be from row 1 to the lowest, non-empty, cell in the column. Fair warning it is currently untested, so try it on a dummy version with known results first. You have to change the constant TargetPercent to match whatever X% you want. This could also be pretty slow since it's using worksheet functions, but I'd assume it would still be faster than by hand. I believe that this will also delete any blank columns
It's also worth noting that in the for loop it is important to go from the right to the left so that you don't accidentally mess up the indices when you're deleting the columns.
Option Explicit
Sub test()
Const TargetPercent = 0.5
Dim nullcells As Long
Dim blankcells As Long
Dim i As Long
Dim lastrow As Long
Dim lastcol
lastcol = ActiveSheet.Cells.Find("*", searchorder:=xlByColumns, searchdirection:=xlPrevious).Column
For i = lastcol To 1 Step -1
lastrow = ActiveSheet.Cells(ActiveSheet.Rows.Count, i).End(xlUp).row
nullcells = Application.CountIf(ActiveSheet.Columns(i), "null")
blankcells = Application.CountBlank(ActiveSheet.Range(Cells(1, i), Cells(lastrow, i)))
If (lastrow - nullcells - blankcells) / lastrow < TargetPercent Then
Columns(i).Delete (xlShiftToLeft)
End If
Next i
End Sub
answered Nov 21 '18 at 19:45
BrotatoBrotato
800212
Again, since I don't know VBA - forgive me... What is the Option Explicit for/ what does it do?
– Uchiha Itachi
Nov 21 '18 at 19:54
Option explicit forces the developer to explicitly declare all of the variables before using them. It helps to check for misspelled variable names in the middle of the code.
– Brotato
Nov 21 '18 at 19:56
Thank you!! You have been tremendously helpful.
– Uchiha Itachi
Nov 21 '18 at 19:57
add a comment |
use a helper column with the following formula:
=COUNTA(A2:D2)-COUNTIF(A2:D2,"null")
This will tell you how many cells have content that is not "null" in any given row
You can then filter on that column
Note: my example assumes data in columns A to D, you will need to adjust for your data.
answered Nov 21 '18 at 19:23
cybernetic.nomadcybernetic.nomad
2,2052820
Thanks, but this would require me to create that counter column over 1500 times. Doesn't really expedite the process for me...
– Uchiha Itachi
Nov 21 '18 at 19:26
1
really?
– cybernetic.nomad
Nov 21 '18 at 19:31
*Barely expedites the process for me...
– Uchiha Itachi
Nov 21 '18 at 19:36
add a comment |
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
The code considers a valid range to be from row 1 to the lowest, non-empty, cell in the column. Fair warning it is currently untested, so try it on a dummy version with known results first. You have to change the constant TargetPercent to match whatever X% you want. This could also be pretty slow since it's using worksheet functions, but I'd assume it would still be faster than by hand. I believe that this will also delete any blank columns
It's also worth noting that in the for loop it is important to go from the right to the left so that you don't accidentally mess up the indices when you're deleting the columns.
Option Explicit
Sub test()
Const TargetPercent = 0.5
Dim nullcells As Long
Dim blankcells As Long
Dim i As Long
Dim lastrow As Long
Dim lastcol
lastcol = ActiveSheet.Cells.Find("*", searchorder:=xlByColumns, searchdirection:=xlPrevious).Column
For i = lastcol To 1 Step -1
lastrow = ActiveSheet.Cells(ActiveSheet.Rows.Count, i).End(xlUp).row
nullcells = Application.CountIf(ActiveSheet.Columns(i), "null")
blankcells = Application.CountBlank(ActiveSheet.Range(Cells(1, i), Cells(lastrow, i)))
If (lastrow - nullcells - blankcells) / lastrow < TargetPercent Then
Columns(i).Delete (xlShiftToLeft)
End If
Next i
End Sub
answered Nov 21 '18 at 19:45
BrotatoBrotato
800212
Again, since I don't know VBA - forgive me... What is the Option Explicit for/ what does it do?
– Uchiha Itachi
Nov 21 '18 at 19:54
Option explicit forces the developer to explicitly declare all of the variables before using them. It helps to check for misspelled variable names in the middle of the code.
– Brotato
Nov 21 '18 at 19:56
Thank you!! You have been tremendously helpful.
– Uchiha Itachi
Nov 21 '18 at 19:57
add a comment |
The code considers a valid range to be from row 1 to the lowest, non-empty, cell in the column. Fair warning it is currently untested, so try it on a dummy version with known results first. You have to change the constant TargetPercent to match whatever X% you want. This could also be pretty slow since it's using worksheet functions, but I'd assume it would still be faster than by hand. I believe that this will also delete any blank columns
It's also worth noting that in the for loop it is important to go from the right to the left so that you don't accidentally mess up the indices when you're deleting the columns.
Option Explicit
Sub test()
Const TargetPercent = 0.5
Dim nullcells As Long
Dim blankcells As Long
Dim i As Long
Dim lastrow As Long
Dim lastcol
lastcol = ActiveSheet.Cells.Find("*", searchorder:=xlByColumns, searchdirection:=xlPrevious).Column
For i = lastcol To 1 Step -1
lastrow = ActiveSheet.Cells(ActiveSheet.Rows.Count, i).End(xlUp).row
nullcells = Application.CountIf(ActiveSheet.Columns(i), "null")
blankcells = Application.CountBlank(ActiveSheet.Range(Cells(1, i), Cells(lastrow, i)))
If (lastrow - nullcells - blankcells) / lastrow < TargetPercent Then
Columns(i).Delete (xlShiftToLeft)
End If
Next i
End Sub
answered Nov 21 '18 at 19:45
BrotatoBrotato
800212
Again, since I don't know VBA - forgive me... What is the Option Explicit for/ what does it do?
– Uchiha Itachi
Nov 21 '18 at 19:54
Option explicit forces the developer to explicitly declare all of the variables before using them. It helps to check for misspelled variable names in the middle of the code.
– Brotato
Nov 21 '18 at 19:56
Thank you!! You have been tremendously helpful.
– Uchiha Itachi
Nov 21 '18 at 19:57
add a comment |
The code considers a valid range to be from row 1 to the lowest, non-empty, cell in the column. Fair warning it is currently untested, so try it on a dummy version with known results first. You have to change the constant TargetPercent to match whatever X% you want. This could also be pretty slow since it's using worksheet functions, but I'd assume it would still be faster than by hand. I believe that this will also delete any blank columns
It's also worth noting that in the for loop it is important to go from the right to the left so that you don't accidentally mess up the indices when you're deleting the columns.
Option Explicit
Sub test()
Const TargetPercent = 0.5
Dim nullcells As Long
Dim blankcells As Long
Dim i As Long
Dim lastrow As Long
Dim lastcol
lastcol = ActiveSheet.Cells.Find("*", searchorder:=xlByColumns, searchdirection:=xlPrevious).Column
For i = lastcol To 1 Step -1
lastrow = ActiveSheet.Cells(ActiveSheet.Rows.Count, i).End(xlUp).row
nullcells = Application.CountIf(ActiveSheet.Columns(i), "null")
blankcells = Application.CountBlank(ActiveSheet.Range(Cells(1, i), Cells(lastrow, i)))
If (lastrow - nullcells - blankcells) / lastrow < TargetPercent Then
Columns(i).Delete (xlShiftToLeft)
End If
Next i
End Sub
answered Nov 21 '18 at 19:45
BrotatoBrotato
800212
The code considers a valid range to be from row 1 to the lowest, non-empty, cell in the column. Fair warning it is currently untested, so try it on a dummy version with known results first. You have to change the constant TargetPercent to match whatever X% you want. This could also be pretty slow since it's using worksheet functions, but I'd assume it would still be faster than by hand. I believe that this will also delete any blank columns
It's also worth noting that in the for loop it is important to go from the right to the left so that you don't accidentally mess up the indices when you're deleting the columns.
Option Explicit
Sub test()
Const TargetPercent = 0.5
Dim nullcells As Long
Dim blankcells As Long
Dim i As Long
Dim lastrow As Long
Dim lastcol
lastcol = ActiveSheet.Cells.Find("*", searchorder:=xlByColumns, searchdirection:=xlPrevious).Column
For i = lastcol To 1 Step -1
lastrow = ActiveSheet.Cells(ActiveSheet.Rows.Count, i).End(xlUp).row
nullcells = Application.CountIf(ActiveSheet.Columns(i), "null")
blankcells = Application.CountBlank(ActiveSheet.Range(Cells(1, i), Cells(lastrow, i)))
If (lastrow - nullcells - blankcells) / lastrow < TargetPercent Then
Columns(i).Delete (xlShiftToLeft)
End If
Next i
End Sub
answered Nov 21 '18 at 19:45
BrotatoBrotato
800212
edited Nov 21 '18 at 19:57
answered Nov 21 '18 at 19:45
BrotatoBrotato
800212
answered Nov 21 '18 at 19:45
BrotatoBrotato
800212
answered Nov 21 '18 at 19:45
BrotatoBrotato
800212
800212
Again, since I don't know VBA - forgive me... What is the Option Explicit for/ what does it do?
– Uchiha Itachi
Nov 21 '18 at 19:54
Option explicit forces the developer to explicitly declare all of the variables before using them. It helps to check for misspelled variable names in the middle of the code.
– Brotato
Nov 21 '18 at 19:56
Thank you!! You have been tremendously helpful.
– Uchiha Itachi
Nov 21 '18 at 19:57
add a comment |
Again, since I don't know VBA - forgive me... What is the Option Explicit for/ what does it do?
– Uchiha Itachi
Nov 21 '18 at 19:54
Option explicit forces the developer to explicitly declare all of the variables before using them. It helps to check for misspelled variable names in the middle of the code.
– Brotato
Nov 21 '18 at 19:56
Thank you!! You have been tremendously helpful.
– Uchiha Itachi
Nov 21 '18 at 19:57
Again, since I don't know VBA - forgive me... What is the Option Explicit for/ what does it do?
– Uchiha Itachi
Nov 21 '18 at 19:54
Again, since I don't know VBA - forgive me... What is the Option Explicit for/ what does it do?
– Uchiha Itachi
Nov 21 '18 at 19:54
Option explicit forces the developer to explicitly declare all of the variables before using them. It helps to check for misspelled variable names in the middle of the code.
– Brotato
Nov 21 '18 at 19:56
Option explicit forces the developer to explicitly declare all of the variables before using them. It helps to check for misspelled variable names in the middle of the code.
– Brotato
Nov 21 '18 at 19:56
Thank you!! You have been tremendously helpful.
– Uchiha Itachi
Nov 21 '18 at 19:57
Thank you!! You have been tremendously helpful.
– Uchiha Itachi
Nov 21 '18 at 19:57
add a comment |
use a helper column with the following formula:
=COUNTA(A2:D2)-COUNTIF(A2:D2,"null")
This will tell you how many cells have content that is not "null" in any given row
You can then filter on that column
Note: my example assumes data in columns A to D, you will need to adjust for your data.
answered Nov 21 '18 at 19:23
cybernetic.nomadcybernetic.nomad
2,2052820
Thanks, but this would require me to create that counter column over 1500 times. Doesn't really expedite the process for me...
– Uchiha Itachi
Nov 21 '18 at 19:26
1
really?
– cybernetic.nomad
Nov 21 '18 at 19:31
*Barely expedites the process for me...
– Uchiha Itachi
Nov 21 '18 at 19:36
add a comment |
use a helper column with the following formula:
=COUNTA(A2:D2)-COUNTIF(A2:D2,"null")
This will tell you how many cells have content that is not "null" in any given row
You can then filter on that column
Note: my example assumes data in columns A to D, you will need to adjust for your data.
answered Nov 21 '18 at 19:23
cybernetic.nomadcybernetic.nomad
2,2052820
Thanks, but this would require me to create that counter column over 1500 times. Doesn't really expedite the process for me...
– Uchiha Itachi
Nov 21 '18 at 19:26
1
really?
– cybernetic.nomad
Nov 21 '18 at 19:31
*Barely expedites the process for me...
– Uchiha Itachi
Nov 21 '18 at 19:36
add a comment |
use a helper column with the following formula:
=COUNTA(A2:D2)-COUNTIF(A2:D2,"null")
This will tell you how many cells have content that is not "null" in any given row
You can then filter on that column
Note: my example assumes data in columns A to D, you will need to adjust for your data.
answered Nov 21 '18 at 19:23
cybernetic.nomadcybernetic.nomad
2,2052820
use a helper column with the following formula:
=COUNTA(A2:D2)-COUNTIF(A2:D2,"null")
This will tell you how many cells have content that is not "null" in any given row
You can then filter on that column
Note: my example assumes data in columns A to D, you will need to adjust for your data.
answered Nov 21 '18 at 19:23
cybernetic.nomadcybernetic.nomad
2,2052820
answered Nov 21 '18 at 19:23
cybernetic.nomadcybernetic.nomad
2,2052820
answered Nov 21 '18 at 19:23
cybernetic.nomadcybernetic.nomad
2,2052820
answered Nov 21 '18 at 19:23
cybernetic.nomadcybernetic.nomad
2,2052820
2,2052820
Thanks, but this would require me to create that counter column over 1500 times. Doesn't really expedite the process for me...
– Uchiha Itachi
Nov 21 '18 at 19:26
1
really?
– cybernetic.nomad
Nov 21 '18 at 19:31
*Barely expedites the process for me...
– Uchiha Itachi
Nov 21 '18 at 19:36
add a comment |
Thanks, but this would require me to create that counter column over 1500 times. Doesn't really expedite the process for me...
– Uchiha Itachi
Nov 21 '18 at 19:26
1
really?
– cybernetic.nomad
Nov 21 '18 at 19:31
*Barely expedites the process for me...
– Uchiha Itachi
Nov 21 '18 at 19:36
Thanks, but this would require me to create that counter column over 1500 times. Doesn't really expedite the process for me...
– Uchiha Itachi
Nov 21 '18 at 19:26
Thanks, but this would require me to create that counter column over 1500 times. Doesn't really expedite the process for me...
– Uchiha Itachi
Nov 21 '18 at 19:26
1
1
really?
– cybernetic.nomad
Nov 21 '18 at 19:31
really?
– cybernetic.nomad
Nov 21 '18 at 19:31
*Barely expedites the process for me...
– Uchiha Itachi
Nov 21 '18 at 19:36
*Barely expedites the process for me...
– Uchiha Itachi
Nov 21 '18 at 19:36
add a comment |
are you looking at the percentage of numerical values or just any cells that arent "null"
– Brotato
Nov 21 '18 at 19:13
@Brotato - some of the cells simply don't have any value in them, but some also have a string value of "null". I want to count both.
– Uchiha Itachi
Nov 21 '18 at 19:18
2
From what I've seen, people here want you to have a code to begin with, and if you're having issues with your code, they can fix it for you. So in your case, without any code at all, people think you just want others to make the code for you..
– Basher
Nov 21 '18 at 19:30
Since you don't know where to start, let's go and make one. First, we need to find the percentage that is populated. Does this mean you want to find the last row of any columns, and use that as a basis? Ex: Column A has 100,000 rows, whereas Column B has 200,000 rows. By using Column B as the "last row", would Column A's percentage be considered 50% filled?
– Basher
Nov 21 '18 at 19:35
@Basher I don't have code to start with because I don't know VBA... As to your second comment: No - I need to figure this out on a column-by-column basis. So if Column A has 100k rows, I need percentage from the 100k; same for Column B with 235K rows and so on.
– Uchiha Itachi
Nov 21 '18 at 19:39