Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and remove blanks
I have a macro that does alot of things. It is used for a spreadsheet in
which the user can paste in info from other programs. In order for this to work my macro must search all used cells, check if their content is purely numeric i.e. "15545" and "4457676,15" and "546 222 111,333 114" are purely numeric whereas "AA1" or "Delta 1" is not pure numeric. If the contents of a cell is purely numeric then the macro shall search the contents inorder to find blanks and re move them. The reason for this is that Excel cannot add e.g. "555 111 222" and "500" since the first number is maltreated due to the blanks. I have written several subs that supposedly did this quuite simple task and I have also tried recording a macro but they always seem to be somewhat wrong. I submit code as examples of what I ahve done and if someone has a piece of code that they know work please post it. I sometimes want to search an entire workbook and sometimes just a worksheet. Code: Private Sub findAndRemoveBlanks() Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _ xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False End Sub Another example: Public Sub findAndRemoveBlanks(s As String) Dim WB As Workbook Dim SH As Worksheet Dim rng, rCell As Range Set WB = ActiveWorkbook Set SH = WB.Sheets(s) Set rng = SH.UsedRange For Each rCell In rng.Cells With rCell If Not IsEmpty(.Value) Then If Not UCase(.Value) Like "*[A-Z]*" Then ..Replace What:=" ", Replacement:="" End If End If End With Next rCell End Sub |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
remove blanks using formula | Excel Worksheet Functions | |||
Find and remove blanks | Excel Programming | |||
Find + remove blanks if cell contains only numbers | Excel Programming | |||
Remove blanks from text | Excel Programming | |||
Remove blanks from list via VBA | Excel Programming |