[Solved] Excel VBA – How to remove columns where less than X% of rows have value [closed]

by


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

3

solved Excel VBA – How to remove columns where less than X% of rows have value [closed]