bug with cell in excel

[bojan0810]

Hi all...

I got file from a client, and it is acting weird. I never seen anything like it.

It has unknown space in cell that makes editing nightmare.

It looks like there is space after texting in cell A1, but there isnt any space. If you move cursor to end of that blank space and hit backspace, it will delete last letter.

I have no idea what is wrong or how to fix it.

Here is sample of file, it is huge, but mostly cells are like that. This is first time I see this.

https://dl.dropboxusercontent.com/u/57916703/Test.xlsx

 

[bojan0810]

Thank you Ron. I didnt tried with clean before...

I made code with Clean as you said, and it works perfectly

Thank you for this

 

[Claus Busch]

Hi Bojan,

Am Sun, 3 Aug 2014 02:34:51 -0700 (PDT) schrieb (e-mail address removed):

I made code with Clean as you said, and it works perfectly

you can also use TextToColumns:

Columns("A:A").TextToColumns Destination:=Range("A1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
FieldInfo:=Array(Array(1, 9), Array(2, 1)),
TrailingMinusNumbers:=True


Regards
Claus B.

 

[Claus Busch]

Hi Bojan,

Am Sun, 3 Aug 2014 11:42:56 +0200 schrieb Claus Busch:

you can also use TextToColumns:

if column B is not empty better use:
Columns("A:A").TextToColumns Destination:=Range("A1"), _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, FieldInfo:=Array(Array(1,
9), _
Array(2, 1), Array(3, 9)), TrailingMinusNumbers:=True


Regards
Claus B.

 

[bojan0810]

Thank you Claus.

What about this one for whole sheet

Sub Macro1()
Dim cell As Range

For Each cell In ActiveSheet.UsedRange
If Not cell.HasFormula Then
cell.Value = Application.Clean(cell.Value)
End If
Next cell

End Sub

 

[Claus Busch]

Hi Bojan,

Am Sun, 3 Aug 2014 04:53:14 -0700 (PDT) schrieb (e-mail address removed):

Sub Macro1()
Dim cell As Range

For Each cell In ActiveSheet.UsedRange
If Not cell.HasFormula Then
cell.Value = Application.Clean(cell.Value)
End If
Next cell

End Sub

if possible avoid looping through the cells.

Better try:
ActiveSheet.UsedRange.Replace what:=Chr(9), replacement:=""


Regards
Claus B.

 

[bojan0810]

Thank worked too.

Thank you Claus.

To be honest, I see first time file like this. No idea how client got to that. Never had problem with that before.

 

[bojan0810]

Dana ponedjeljak, 4. kolovoza 2014. 22:57:01 UTC+2, korisnik Ron Rosenfeld napisao je:

Oh heck, if you want to use VBA code to do this, just use, on the errant sheet:



Cells.Replace what:=Chr(9), replacement:="", lookat:=xlPart



Although Replace doesn't work from the User interface for the tab character, it seems to work fine using VBA code.

thanks for this too.