CODEWITHSUNDEEP
excelvba
Chris Birmingham
Chris Birmingham

Reputation: 49

How to find "0" values rather than empty cells

I have a spreadsheet containing 8 columns of data, A-H, each of which is a different length and may contain values of "0" which I need deleting. Rather than do this with a loop, I'm trying to do this using the .Find method but I'm becoming unstuck as the find method keeps classifying empty cells (where a column is shorter than the max length column) as zeros. When the function finds one of these cells it selects and deletes a cell at the top of that column rather than selecting the specific cell. I've tried using my code using values other than 0 and it works fine so it's a problem with excel classifying empty cells as "0".

Is there a way to search specifically for the value "0"? I've tried using str(0) to specify a string value of "0" but get the same result. A subset of my code as it stands is:

Row = wf.Max(Cells(Rows.Count,1).End(xlUp).Row, Cells(Rows.Count,8).End(xlUp_.Row

'Originally I had zero = 0 or "0" but I tried the following to get a string version
zero = Str(0)
zerofix = Right(zero,1)

Do While Check_Val_Existence(zerofix,Row) = True
   Set aCell = ActiveSheet.Range(Cells(1,1), Cells(Row,8)).Find(What:=zerofix,LookIn:=xlValues)
   If Not aCell Is Nothing Then
      aCell.Delete Shift:=xlUp
   End If
Loop

where

Function Check_Val_Existence(ByVal Srch, ByVal Row) As Boolean

Dim rFnd As Range

Set rFnd = ActiveSheet.Range(Cells(1,1), Cells(Row,8)).Find(What:=Srch)
If Not rFnd Is Nothing Then
   Check_Val_Existence = True
Else
   Check_Val_Existence = False
End If

End Function

I'd rather not have to loop through the code and search each column in turn but it's beginning to look like I might have to do just that.

Upvotes: 2

Views: 6982

Answers (3)

Siddharth Rout
Siddharth Rout

Reputation: 149295

Try this

Sheet1.Cells.Replace What:="0", Replacement:="", LookAt:=xlwhole, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

This will clear all cells which have "0"

If you want to delete the cells which have "0" then you can also use .Find and .FindNext. See this link

Topic: .Find and .FindNext In Excel VBA

Link: http://www.siddharthrout.com/2011/07/14/find-and-findnext-in-excel-vba/

Quote from that link

In this tutorial, I will stress on how to use .Find to make your search faster.

The syntax of .Find is

expression.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

Where

Expression (Required): is any valid range Object. So if we take the above example then the range would be Range(“A1:A” & lastRow)

What (Optional Variant): is the “Search value”

After (Optional Variant): The cell after which you want the search to begin.

LookIn (Optional Variant): The type of information. (xlValues or xlFormulas)

LookAt (Optional Variant): Can be one of the following XlLookAt (constants): xlWhole or xlPart.

SearchOrder (Optional Variant): Can be one of the following XlSearchOrder constants: xlByRows or xlByColumns.

SearchDirection: Can be one of these XlSearchDirection constants. xlNext default xlPrevious

MatchCase (Optional Variant): True to make the search case sensitive. The default value is False.

MatchByte (Optional Variant): Used only if you’ve selected or installed double-byte language support. True to have double-byte characters match only double-byte characters. False to have double-byte characters match their single-byte equivalents.

SearchFormat (Optional Variant): The search format.

Upvotes: 2

Robert Ilbrink
Robert Ilbrink

Reputation: 7953

My suggestion is to do a general replace of 0 to =0/0, which should give an error (and still gives you a way to restore the values back to 0 if needed, due to the unique calculation), then do a find/select special errors.

Upvotes: 0

brettdj
brettdj

Reputation: 55682

Rather then work with all cells, do the prelim work to define the range of interest - i.e. cells that have numbers in them either entered as constants or as formulae. This excludes your blank cels from the search which will both speed up your code, and eliminate the false flags.

In this example rng3 returns the cells of interest on the ActiveSheet. You would then run your Find routine on this range

Sub LookAtZeros()
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
On Error Resume Next
Set rng1 = Cells.SpecialCells(xlCellTypeConstants, xlNumbers)
Set rng2 = Cells.SpecialCells(xlCellTypeFormulas, xlNumbers)
On Error GoTo 0
If Not rng1 Is Nothing Then
If Not rng2 Is Nothing Then
Set rng3 = Union(rng1, rng2)
Else
Set rng3 = rng1
End If
Else
If Not rng2 Is Nothing Then Set rng3 = rng2
End If
If Not rng3 Is Nothing Then
MsgBox "Range with numeric constants and/or numeric formulae is " & rng3.Address(0, 0)
Else
MsgBox "no numbers", vbCritical
End If
End Sub

Upvotes: 0

Related Questions