i need a macro that will select 0's in columns h-j.
- thanks
i need a macro that will select 0's in columns h-j.
- thanks
Re: select "0's"
And once selected, what do you need to do with them?
lenze
Re: select "0's"
zotee98,
Is that a zero or an oh?
Does the cell contain anything other than those round things?
What do you want to do with them after you select them?
Jim
Re: select "0's"
i want to delete the zeros. is there perhaps a better way? i need the cells to be completly blank.
Re: select "0's"
Here is a macro to select them (your original question).
Sub SelectZerosColsH2J()
Dim c As Range, AllZeros As Range, LastRow As Long
LastRow = WorksheetFunction.Max(Cells(Rows.Count, 8).End(xlUp).Row, _
Cells(Rows.Count, 9).End(xlUp).Row, Cells(Rows.Count, 10).End(xlUp).Row)
For Each c In Range("H1:J" & LastRow)
If c = 0 And Len(c) > 0 Then
If AllZeros Is Nothing Then
Set AllZeros = c
Else
Set AllZeros = Union(AllZeros, c)
End If
End If
Next c
AllZeros.Select
End Sub
Display More
To delete the zeros, change the "AllZeros.Select" to "AllZeros.Clear"
Re: select "0's"
But if you just want to clear the zeros, use:
Sub SelectZerosColsH2J()
Dim c As Range, LastRow As Long
LastRow = WorksheetFunction.Max(Cells(Rows.Count, 8).End(xlUp).Row, _
Cells(Rows.Count, 9).End(xlUp).Row, Cells(Rows.Count, 10).End(xlUp).Row)
For Each c In Range("H1:J" & LastRow)
If c = 0 And Len(c) > 0 Then c.Clear
Next c
End Sub
Display More
NOTE: (EDIT) Using simply
will also clear the zeros, but it also will clear the formatting from any empty cells. If you want to keep the formatting (including formatting in cells with zeros in them, then use
Re: select "0's"
Another choice:
Re: select "0's"
Leave the zeros in the cells as empty cells also have a value of zero. Hide them via Tools>Options>View - Zero Values or use a Custom Number Format to hide them on a cell-by-cell basis.
Or, if you really wnat to, try simply using Edit>Replace and Replace 0 with nothing (leave replace with blank.)
Don’t have an account yet? Register yourself now and be a part of our community!