VBA Code to delete Rows with empty rows or spaces will not delete one particular row

[OilEconomist]

Thanks in advance for your assistance. My objective is to use VBA to delete rows which are empty/blank or when you remove the spaces, it is blank/empty. It will delete row 4 which has spaces, but it will not delete row 2 which has spaces. This is based on a data set I downloaded. I changed the values in the rows which had values and then in row 4 I inserted blank spaces. Row 2 is the one that has blank spaces in the original data set.

Also, if anyone can resolve this issue, if there is a faster way as this code is very slow.

This is my methodology
(1) Find the last row
(2) Set the range
(3) Within a loop: I check if I trim the cell, and it's empty, I clear the contents (the spaces). The cell now becomes blank
(4) In the Range, if any of the cells are empty/blank, I delete them.

As previously stated, once I trim the cell, and if that trimmed cell is empty/blank, it does not get deleted.

Here is a set of sample data. Check Cell A2 in particular as that is the one that will not delete.

Stocks - Analysis Tool, Sub - 52 Week Lows - (Active).xlsm
	A
1	Blue
2
3	Black
4
5	White Green
6
7
8	Yellow Hello here
Sheet5

Sub TestEmpty()

  '_________________________________________________________________________________________________
  'Turn off alerts, screen UDs, and automatic calculation
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Application.Calculation = xlManual
    
    
    
  '_________________________________________________________________________________________________
  'Dim
     Dim LastRow As Long
     Dim aCell As Range, oCell As Range, Rng As Range
     
     
     
  '_________________________________________________________________________________________________
    'Get last row and set Range
      LastRow = Cells.Find(What:="*", After:=Range("A1"), LookAt:=xlPart, LookIn:=xlFormulas, _
             SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
      Set Rng = Range("A1:A" & LastRow)
    
    'Check if blank spaces, and nothing else
      For Each aCell In Rng
        If Trim(Range("A" & aCell.Row)) = "" Then Range("A" & aCell.Row).Clear
      Next

     On Error Resume Next
      Rng.Select
      Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
      Range("A1").Select
      
  
  '_________________________________________________________________________________________________
  'Turn off alerts, screen UDs, and automatic calculation
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Application.Calculation = xlManual
    
End Sub

 

[alansidman]

Guessing that row two has a non printed character in it and is not really blank or empty. When I load your data and do a check of the length, it returns 1 for row 2 and zero for 4, 6 and 7. You will need to test for non printed characters like a line feed or carriage return, etc. in addition to empty.

 

[OilEconomist]

Guessing that row two has a non printed character in it and is not really blank or empty. When I load your data and do a check of the length, it returns 1 for row 2 and zero for 4, 6 and 7. You will need to test for non printed characters like a line feed or carriage return, etc. in addition to empty.

Thanks for your response @alansidman. I searched the internet for the topic, but I could not find anything to resolve the issue. Any leads you can provide will be much appreciated.

 

[alansidman]

How to Remove Non-Printable Characters in Excel? - ExcelDemy

Using Functions like CLEAN, SUBSTITUTE, features like Find & Replace, and VBA Macro Code to remove non-printable characters in Excel.

www.exceldemy.com

 

[Alex Blakenburg]

I think the non-printing characters disappeared in the XL2BB extract process

The most common problem character is code 160 (non-breaking space).
If you want to, try this line instead of yours:

        If Trim(Replace(Range("A" & aCell.Row), Chr(160), "")) = "" Then Range("A" & aCell.Row).Clear

 

[OilEconomist]

I think the non-printing characters disappeared in the XL2BB extract process

The most common problem character is code 160 (non-breaking space).
If you want to, try this line instead of yours:

        If Trim(Replace(Range("A" & aCell.Row), Chr(160), "")) = "" Then Range("A" & aCell.Row).Clear

Thanks @Alex Blakenburg I just tried it and it worked. I will check out with my actual data set and be sure to update you. Thanks so much!

 

[OilEconomist]

How to Remove Non-Printable Characters in Excel? - ExcelDemy

Using Functions like CLEAN, SUBSTITUTE, features like Find & Replace, and VBA Macro Code to remove non-printable characters in Excel.

www.exceldemy.com

Thanks @alansidman. I will check this out!

 

[Alex Blakenburg]

Thanks @Alex Blakenburg I just tried it and it worked. I will check out with my actual data set and be sure to update you. Thanks so much!

You're Welcome and please do let us know what you find when you check your data.

 

[johnnyL]

Also fix the ending of the code.

  '_________________________________________________________________________________________________
  'Turn off alerts, screen UDs, and automatic calculation
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Application.Calculation = xlManual
    
End Sub

That needs to be corrected to turn those settings back on.

 

[OilEconomist]

Also fix the ending of the code.

  '_________________________________________________________________________________________________
  'Turn off alerts, screen UDs, and automatic calculation
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Application.Calculation = xlManual
   
End Sub

That needs to be corrected to turn those settings back on.

Thank you @johnnyL. Oversite by me.