VBA remove hidden text

My code should remove all rows with blanks in column C.
However, only some get removed. I found that those remaining actually have something in them, because if I select and "DELETE" the cells, the code runs perfectly.

Can someone help add removing hidden characters so that it will run as intended?
Thanks!!

Sub delnozone()
Application.ScreenUpdating = False
Reduced_Data.Activate


    Dim i As Long
    i = 2
    Do Until i > Cells(Cells.Rows.Count, "A").End(xlUp).Row
        If Cells(i, "c").Text = "" Then
            Rows(i).Delete
        Else
            i = i + 1
        End If
    Loop
End Sub

Open in new window

Euro5Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

FlysterCommented:
Try this:
Sub delnozone()
Application.ScreenUpdating = False
Reduced_Data.Activate


    Dim i As Long
    i = 2
    Do Until i > Cells(Cells.Rows.Count, "A").End(xlUp).Row
        If LEN(Cells(i, "c").Text)>0 Then
            Rows(i).Delete
        Else
            i = i + 1
        End If
    Loop
End Sub

Open in new window

Flyster
Euro5Author Commented:
It just runs and runs. I had to stop it twice...stuck on
Rows(i).Delete
FlysterCommented:
My apologies. Code should be less than one, not more than zero.
Sub delnozone()
Application.ScreenUpdating = False
Reduced_Data.Activate


    Dim i As Long
    i = 2
    Do Until i > Cells(Cells.Rows.Count, "A").End(xlUp).Row
        If Len(Cells(i, "c").Text) < 1 Then
            Rows(i).Delete
        Else
            i = i + 1
        End If
    Loop
End Sub

Open in new window

Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

Saurabh Singh TeotiaCommented:
Euro,

Can you do me a favour on cells which appear blank..in the adjacent cell..can you apply this formula...assuming its in a4..so in b4..

=code(a4)

Then can you tell me the number as a result which you get as answer...

Saurabh...
Euro5Author Commented:
Saurabh,
69,70,71,73,80,83 those are all the codes.
Euro5Author Commented:
Flyster, This did not remove the blanks.
FlysterCommented:
Would it be possible to provide a sample workbook with an effective cell?
aikimarkCommented:
I usually recommend deleting from the bottom/end/last to the top/start/first.  Strange things can happen when you delete items inside your iteration loop.
Saurabh Singh TeotiaCommented:
Euro5,

Can you post your sample file as it's important to look before re-writing code for you...

Saurabh...
Euro5Author Commented:
I need to remove the rows where the ZONE is blank.
It will remove all those but where services are IE, IP, IPF, IEF....
I assume its because there are hidden characters.
I appreciate any help!
Sample-blank-zones.xlsx
Saurabh Singh TeotiaCommented:
You can use the following code which will do what you are looking for..

Sub delnozone()
Application.ScreenUpdating = False



    Dim i As Long
    i = 2
    Do Until i > Cells(Cells.Rows.Count, "A").End(xlUp).Row
        If Replace(Cells(i, "b").Text, Chr(32), "") = "" Then
            Rows(i).Delete
        Else
            i = i + 1
        End If
    Loop
End Sub

Open in new window


Saurabh...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Euro5Author Commented:
Works! Thank you!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.