ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Find and remove blanks (https://www.excelbanter.com/excel-programming/366130-find-remove-blanks.html)

Nick London

Find and remove blanks
 
I have a sub that is supposed to find and remove blanks from a spreadsheet.
However it does not work anymore. I do not understand what is wrong with it.
The pupose of the sub is to search a spreadsheet for blanks and if the
contents of a cell is purely numeric then replace these blanks with nothing
e.g. if a cell contains 100 000 000 it shall be replaced by 100000000 but if
the contents are AA 3 it should not be changed. I might also add that I am
pasting in the info from another program into the spreadsheet. I do not know
if that has any importance. The code is:

Private Sub findAndRemoveBlanks()
Dim WB As Workbook
Dim SH As Worksheet
Dim rng, rCell As Range
Set WB = ActiveWorkbook
Set SH = WB.Sheets("Beräkning")
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

The code works fine if you write something in a cell and then check it but
it does not seem to work when pasting. If anyone has any idea how to solve
this I would be most greatful for any assistance that you can give me! Thank
you!

Simon Lloyd[_814_]

Find and remove blanks
 

Hi theres plenty of stuff here for removing blanks that you should be
able to modify, its all self explanatary with worked examples.
http://www.mvps.org/dmcritchie/excel/delempty.htm

Regards,
Simon


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=557882



All times are GMT +1. The time now is 12:24 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com