Excel VBA - How to remove columns where less than X% of rows have value

Question

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.

are you looking at the percentage of numerical values or just any cells that arent "null" — Jchang43, 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
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
Thanks @Brotato - that's helpful for finding/setting Range for each column!! — Uchiha Itachi, Nov 21 '18 at 19:45
Thought I'd throw part of it in my answer instead, but for those curious [this was the link](https://stackoverflow.com/a/11169920/10361731) — Jchang43, Nov 21 '18 at 19:47
Brotato is too fast for me, didn't get to post my answer :P - Anyway, if running his code is slow as you have thousands of colums and rows, try adding this and add the line "OptimizeVBA True" at the very beginning of his code, and "OptimizeVBA False" at the end: [link](https://analystcave.com/excel-improve-vba-performance/) — Basher, Nov 21 '18 at 20:14

score 3 · Answer 1 · answered Nov 21 '18 at 19:23

3

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.nomad

6,100
3
18
31

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?](https://superuser.com/questions/1373214/how-to-fill-a-large-series-over-10-000-rows-in-microsoft-excel-without-draggin/1373218#1373218) – cybernetic.nomad Nov 21 '18 at 19:31
*Barely expedites the process for me... – Uchiha Itachi Nov 21 '18 at 19:36

Jchang43 · Accepted Answer · 2018-11-21T19:57:14.287

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

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. — Jchang43, Nov 21 '18 at 19:56

Excel VBA - How to remove columns where less than X% of rows have value

2 Answers2