PDA

View Full Version : Solved: Find & Replace problem



slang
10-01-2008, 06:21 AM
I have a database that has zero values that I need to remove in specific columns.
I need to find and replace any cells that contain only a 0 value within a specific range or column and replace it with ""

I want to do this in VB but I need to figure out how to do a find and replace on only a specific column instead of it wanting to check the entire sheet.

Thanks oh Excel gods....:bow:
</IMG>

Bob Phillips
10-01-2008, 06:23 AM
Post a workbook, the words are not that clear.

slang
10-01-2008, 07:07 AM
Sorry about that. :doh:
The data in yellow is what I would like to remove without removing any zero values everywhere else in the sheet.
These two columns should never contain just a zero value but could contain values like 500, 20, or text similar to "Location had 0 number of signs"

Thanks again.

X10A
10-01-2008, 07:24 AM
Hi slang,
Is it a must to be done using VB? Otherwise, you can just select column L and M, use the find and replace function. The find value will be 0 and just leave the replace blank.

GTO
10-01-2008, 09:28 PM
Case you do want this in vba, this should work

Option Explicit
Sub ElimZeros()
Dim rngLookIn As Range
Set rngLookIn = _
Sheet1.Range(Cells(2, 12), _
Cells( _
Application.WorksheetFunction.Max(Cells(Rows.Count, 12).End(xlUp).Row, _
Cells(Rows.Count, 13).End(xlUp).Row), 13))
rngLookIn.Cells.Replace What:="0", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
End Sub

slang
10-02-2008, 03:25 AM
Thanks GTO!
I would like it to run in VBA because I need this to be part of a patch that I will send to multiple users to fix their database local.

Your code works great with the exception that it removes all zeros even if they are part of a number like 500 or 250 etc. I just replaced Lookat:=xlpart with xl:=whole and it works great.:thumb

Thanks a lot!!!!!:beerchug:

GTO
10-02-2008, 04:45 AM
OOPS; glad you caught my errorerer and got 'er working :-)