Cleaning up data – getting rid of blank or 0 values in Excel

This function is designed to help clean up a spreadsheet if you want to either delete or hide cells that have 0 or empty values. How this works:

I select a range of data that I want to get 0s and blank cells out of (it doesn’t have to be a column) and run the macro.

What if I only wanted 0s, or blank cells only? What if I wanted to delete the entire row that has that cell?

In the code below, you can change these options based on what you want the function to do. I have created two variables called option1 and option2. Their values are bolded in red.

If I change option1 from 1 to 2, then only 0 value cells will be affected, a value of 3 will mean only blank cells are.

For option 2 I can choose to hide the entire row (1), hide the entire column (2), delete the row (3), delete the column (4), clear the cells (5), delete the specific cells and shift cells up (6), or delete the cells and shift left (7).

Changing the values in red allows you to make any of the above changes.

The code for the macro is below. I suggest assigning a shortcut for this macro to run it quickly.
———————————————————————————————————————–

Sub cleanupdata()

Dim option1, option2 As Integer

option1 = 1
‘Option1
‘1 = blanks and 0s
‘2 = 0s only
‘3 = blanks only

option2 = 1
‘Option2
‘1 = hide row
‘2 = hide column
‘3 = delete row
‘4 = delete column
‘5 = clear cells
‘6 = delete cell, shift up
‘7 = delete cell, shift left

Dim activerange As Range
Dim cl As Range
Dim selectedcells As Collection
Dim numberofitems As Integer
Set selectedcells = New Collection

Set activerange = Selection

For Each cl In activerange

Select Case option1

    Case Is = 1 ‘Blanks and 0s
            If Len(cl) = 0 Or (Len(cl) > 0 And cl = 0) Then
                selectedcells.Add cl
               
            End If
           
    Case Is = 2 ‘0s only
            If Len(cl) > 0 And cl = 0 Then
                selectedcells.Add cl
            End If
                     
    Case Is = 3 ‘Blanks only
            If Len(cl) = 0 Then
                selectedcells.Add cl
            End If
           
End Select
Next cl

numberofitems = selectedcells.Count

On Error Resume Next
Select Case option2
    Case Is = 1
        For counter = 1 To numberofitems
        selectedcells.Item(counter).EntireRow.Hidden = True
        Next counter
    Case Is = 2
        For counter = 1 To numberofitems
        selectedcells.Item(counter).EntireColumn.Hidden = True
        Next counter
    Case Is = 3
        For counter = 1 To numberofitems
        selectedcells.Item(counter).EntireRow.Delete
        Next counter
    Case Is = 4
        For counter = 1 To numberofitems
        selectedcells.Item(counter).EntireColumn.Delete
        Next counter
    Case Is = 5
        For counter = 1 To numberofitems
        selectedcells.Item(counter).Delete
        Next counter
    Case Is = 6
        For counter = 1 To numberofitems
        selectedcells.Item(counter).Select
        Selection.Delete shift:=xlUp
        Next counter
    Case Is = 7
        For counter = 1 To numberofitems
        selectedcells.Item(counter).Select
        Selection.Delete shift:=xlToLeft
        Next counter
End Select

End Sub

———————————————————————————————————————–