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

 

[mohadin]

Hi
I guess
this what you asking for

Sub test()
    Dim zrng As Range, c As Range
    Dim lr
       lr = Cells(Rows.Count, Selection.Column).End(xlUp).Row
        For Each c In Selection(lr).Offset(-lr + 1).Resize(lr).Cells
            If c.Value = 0 And Not IsEmpty(c) Then
                If zrng Is Nothing Then
                    Set zrng = c
                Else
                    Set zrng = Union(zrng, c)
                End If
            End If
        Next
        If Not zrng Is Nothing Then zrng.Select
End Sub

 

[excelakos]

Hi
I guess
this what you asking for

Sub test()
    Dim zrng As Range, c As Range
    Dim lr
       lr = Cells(Rows.Count, Selection.Column).End(xlUp).Row
        For Each c In Selection(lr).Offset(-lr + 1).Resize(lr).Cells
            If c.Value = 0 And Not IsEmpty(c) Then
                If zrng Is Nothing Then
                    Set zrng = c
                Else
                    Set zrng = Union(zrng, c)
                End If
            End If
        Next
        If Not zrng Is Nothing Then zrng.Select
End Sub

Oh Lord!!! Mohadin you are 90% there!!! Yes the action is restrained in the active column 100% as desired, BUT it goes and select all cells with zeros below the already top selected cell. See attached screen shot

 

[mohadin]

Sub testr()
    Dim zrng As Range, c As Range
    Dim lr
       lr = Cells(Rows.Count, Selection.Column).End(xlUp).Row
        For Each c In Selection.Offset(-lr + (Selection.Row + 2)).Resize(lr).Resize(lr).Cells
            If c.Value = 0 And Not IsEmpty(c) Then
                If zrng Is Nothing Then
                    Set zrng = c
                Else
                    Set zrng = Union(zrng, c)
                End If
            End If
        Next
        If Not zrng Is Nothing Then zrng.Select
End Sub

 

[excelakos]

Sub testr()
    Dim zrng As Range, c As Range
    Dim lr
       lr = Cells(Rows.Count, Selection.Column).End(xlUp).Row
        For Each c In Selection.Offset(-lr + (Selection.Row + 2)).Resize(lr).Resize(lr).Cells
            If c.Value = 0 And Not IsEmpty(c) Then
                If zrng Is Nothing Then
                    Set zrng = c
                Else
                    Set zrng = Union(zrng, c)
                End If
            End If
        Next
        If Not zrng Is Nothing Then zrng.Select
End Sub

Now we get 1004 error

 

[mohadin]

For Each c In Selection.Offset(-lr + (Selection.Row + 2)).Resize(lr).Cells

Sorry!

 

[Tupe77]

Maybe this would suit your purpose?

Sub ZeroesV2()
    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 = Selection

        Set SearchRNG2 = SearchRNG.ListObject.ListColumns(SearchRNG.Column - SearchRNG.ListObject.DataBodyRange.Column + 1).DataBodyRange
    
        For Each Cell In SearchRNG2
            If Cell.Value = 0 And Not IsEmpty(Cell) And Cell.Row > SearchRNG.Row 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
        Else
            MsgBox "No zero values were not found in the selected column"
        End If
End Sub

 

[Tupe77]

I understand wrong, sorry.
Try this:

Sub ZeroesV3()
    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 = Selection

        Set SearchRNG = SearchRNG.ListObject.ListColumns(SearchRNG.Column - SearchRNG.ListObject.DataBodyRange.Column + 1).DataBodyRange
    
        For Each Cell In SearchRNG
            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
        Else
            MsgBox "No zero values were not found in the selected column"
        End If
End Sub

 

[excelakos]

I understand wrong, sorry.
Try this:

Sub ZeroesV3()
    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 = Selection

        Set SearchRNG = SearchRNG.ListObject.ListColumns(SearchRNG.Column - SearchRNG.ListObject.DataBodyRange.Column + 1).DataBodyRange
   
        For Each Cell In SearchRNG
            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
        Else
            MsgBox "No zero values were not found in the selected column"
        End If
End Sub

Bullseye!!!! That nailed it!!!! Thank you both so much. @mohadin thank you too, seems like you pushed each other to the solution!!!

 

[mohadin]

You are welcome
And thank you for the feedback
Be happy & safe

 

[Tupe77]

Bullseye!!!! That nailed it!!!! Thank you both so much. @mohadin thank you too, seems like you pushed each other to the solution!!!

Thank you for the feedback!
Nice that we were able to help.