VBA For Selecting Cells WIth Zero

excelakos

Board Regular
Joined
Jan 22, 2014
Messages
67
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!!


VBA Code:
Sub SelectBlankCells()


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

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

PatrickRamon

New Member
Joined
Feb 23, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jan 22, 2014
Messages
67
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

Board Regular
Joined
Nov 26, 2020
Messages
117
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Does this work for you?

VBA Code:
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

Board Regular
Joined
Nov 26, 2020
Messages
117
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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.

VBA Code:
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

Board Regular
Joined
Jan 22, 2014
Messages
67
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.

VBA Code:
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

Board Regular
Joined
Nov 26, 2020
Messages
117
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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.

VBA Code:
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

Board Regular
Joined
Jan 22, 2014
Messages
67
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.

VBA Code:
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

Board Regular
Joined
Nov 26, 2020
Messages
117
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Board Regular
Joined
Jan 22, 2014
Messages
67
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?
 

Attachments

  • Mrexcel2.png
    Mrexcel2.png
    80.6 KB · Views: 7
  • Mrexcel1.png
    Mrexcel1.png
    96.4 KB · Views: 7
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,575
Messages
5,765,188
Members
425,266
Latest member
CPAgirl

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top