VBA For Selecting Cells WIth Zero

[excelakos]

Hi everyone!
So i have found below code to help me select all blank cells in a range.
Is there a way to make it select all cells having zero (0) value?
Thank you in advance!!

Sub SelectBlankCells()


    Range("tablename[columnname]").Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
      
End Sub

 

[PatrickRamon]

Hi,

Without seeing some sample data, it is difficult to determine where the zero values are. If they are all in a single column then you can give this code a try.

It checks for zero values along column "A" and deletes the entire row if found.

Sub DeleteRow()
Dim r As Long
Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For r = LastRow To 1 Step -1
If Cells(r, 1) = 0 Then
Rows(r).Delete
End If
Next r
End Sub

 

[excelakos]

Hello Patrick! First of all thank you for your time. Please have in mind i do not wish to delete "zeros". I just wan to select them all at once instaed of selecting each cell with Ctrl and keep going till the hell freezes...The code i gave in my post does exactly that with blank cells. Selects all blank cells in any table, in any column of the table i wish. So i need this to be done with cells having zeros. Thank you anyway!

 

[Tupe77]

Does this work for you?

Sub Zeroes()
    Dim SearchRNG As Range, ZeroRNG As Range, Cell As Range
    Dim wb As Workbook, wbs As Worksheet
        Set wb = ThisWorkbook
        Set wbs = wb.ActiveSheet
        Set SearchRNG = wbs.UsedRange
        
        'Set SearchRNG = Selection
    
        For Each Cell In SearchRNG
            If Cell.Value = 0 Then
                If ZeroRNG Is Nothing Then
                    Set ZeroRNG = Cell
                Else
                    Set ZeroRNG = Union(ZeroRNG, Cell)
                End If
            End If
        Next
        
        If Not ZeroRNG Is Nothing Then ZeroRNG.Select
End Sub

 

[Tupe77]

My apologies!
I forgot again that Excel counts empty cells as Zero.
So my first code also selects empty cells.

Here's a newer and hopefully better version.

Sub Zeroes()
    Dim SearchRNG As Range, ZeroRNG As Range, Cell As Range
    Dim wb As Workbook, wbs As Worksheet
        Set wb = ThisWorkbook
        Set wbs = wb.ActiveSheet
        Set SearchRNG = wbs.UsedRange
        
        'Set SearchRNG = Selection
    
        For Each Cell In SearchRNG
            'If Cell.Value = 0 Then
            If Cell.Value = 0 And Not IsEmpty(Cell) Then
                If ZeroRNG Is Nothing Then
                    Set ZeroRNG = Cell
                Else
                    Set ZeroRNG = Union(ZeroRNG, Cell)
                End If
            End If
        Next
        
        If Not ZeroRNG Is Nothing Then ZeroRNG.Select
End Sub

 

[excelakos]

My apologies!
I forgot again that Excel counts empty cells as Zero.
So my first code also selects empty cells.

Here's a newer and hopefully better version.

Sub Zeroes()
    Dim SearchRNG As Range, ZeroRNG As Range, Cell As Range
    Dim wb As Workbook, wbs As Worksheet
        Set wb = ThisWorkbook
        Set wbs = wb.ActiveSheet
        Set SearchRNG = wbs.UsedRange
       
        'Set SearchRNG = Selection
   
        For Each Cell In SearchRNG
            'If Cell.Value = 0 Then
            If Cell.Value = 0 And Not IsEmpty(Cell) Then
                If ZeroRNG Is Nothing Then
                    Set ZeroRNG = Cell
                Else
                    Set ZeroRNG = Union(ZeroRNG, Cell)
                End If
            End If
        Next
       
        If Not ZeroRNG Is Nothing Then ZeroRNG.Select
End Sub

Hi Tupe! thank you too for your effort! Well it does the job partially! Picture this. I have already worked in specific cells in a specific column. And then i need to run the macro of yours in order to select all cells with zero in the active column. Is there any way to alter your code to work on any active column of a table? Thank you anyways

 

[Tupe77]

I'm sorry, but I'm not sure what you mean by "active column". Is the entire column then selected or just the cell in it?
When you press Delete with "active column", all column data is deleted? In that case, my code has such an option already.

It was just disabled.

Sub Zeroes()
    Dim SearchRNG As Range, ZeroRNG As Range, Cell As Range
    Dim wb As Workbook, wbs As Worksheet
        Set wb = ThisWorkbook
        Set wbs = wb.ActiveSheet
        'Set SearchRNG = wbs.UsedRange
        
        Set SearchRNG = Selection ' Finds zero values in the selected range.
    
        For Each Cell In SearchRNG
            'If Cell.Value = 0 Then
            If Cell.Value = 0 And Not IsEmpty(Cell) Then
                If ZeroRNG Is Nothing Then
                    Set ZeroRNG = Cell
                Else
                    Set ZeroRNG = Union(ZeroRNG, Cell)
                End If
            End If
        Next
        
        If Not ZeroRNG Is Nothing Then ZeroRNG.Select
End Sub

 

[excelakos]

I'm sorry, but I'm not sure what you mean by "active column". Is the entire column then selected or just the cell in it?
When you press Delete with "active column", all column data is deleted? In that case, my code has such an option already.

It was just disabled.

Sub Zeroes()
    Dim SearchRNG As Range, ZeroRNG As Range, Cell As Range
    Dim wb As Workbook, wbs As Worksheet
        Set wb = ThisWorkbook
        Set wbs = wb.ActiveSheet
        'Set SearchRNG = wbs.UsedRange
       
        Set SearchRNG = Selection ' Finds zero values in the selected range.
   
        For Each Cell In SearchRNG
            'If Cell.Value = 0 Then
            If Cell.Value = 0 And Not IsEmpty(Cell) Then
                If ZeroRNG Is Nothing Then
                    Set ZeroRNG = Cell
                Else
                    Set ZeroRNG = Union(ZeroRNG, Cell)
                End If
            End If
        Next
       
        If Not ZeroRNG Is Nothing Then ZeroRNG.Select
End Sub

When i run the macro, cells with zeros were not only selected in the active column but in other cells elsewhere in the table range. By active column i mean that before i run the macro i have already selected single or multiple cells in a specific column in the table range. More particularly table is named "t_Main" and the column labeled "Group Modifier". So i need macro to "see" the column of the selected cells and then select all cells with zero in this same column. I hope this makes the picture for you clearer. Thank you once again.

 

[Tupe77]

Did you try that new version? It was changed, it retrieves from the selected area. If you select one or more columns, only those columns will be searched. Or Do you mean that based on a previous search, cells from different columns have been selected and search should be retrieved from those columns?

 

[excelakos]

Did you try that new version? It was changed, it retrieves from the selected area. If you select one or more columns, only those columns will be searched. Or Do you mean that based on a previous search, cells from different columns have been selected and search should be retrieved from those columns?

I attach 2 print screens. In first you see that my last action has cells selected in column labeled "Group Modifier" I have done my work with cells showing "100.0%". Next i need to run the macro to select all cells having zeros in same column.
In Print screen 2 you see that cells with zeros are selected in other parts of the table too...Perhaps it has something to do with the "Union" attribute of yours?