asked on

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

Flyster

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

Euro5

ASKER

It just runs and runs. I had to stop it twice...stuck on
Rows(i).Delete

Flyster

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

Saurabh Singh Teotia

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...

Euro5

ASKER

Saurabh,
69,70,71,73,80,83 those are all the codes.

Euro5

ASKER

Flyster, This did not remove the blanks.

Flyster

Would it be possible to provide a sample workbook with an effective cell?

aikimark

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 Teotia

Euro5,

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

Saurabh...

Euro5

ASKER

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

ASKER CERTIFIED SOLUTION

Saurabh Singh Teotia

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Euro5

ASKER

Works! Thank you!!