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

 

[OilEconomist]

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

Thanks @Alex Blakenburg. Although I had only one line with that issue, it worked on my original data set so I marked your solution as correct. If I have any further issues, I will repost the question here or start a new thread. Thanks once again!

 

[Alex Blakenburg]

If you can share your workbook via one of the sharing platforms and maybe highlight some of the problem rows, we are happy to take a look. Unfortunately, XL2BB does not seem to transfer some of these irregular characters in their original form.

 

[OilEconomist]

If you can share your workbook via one of the sharing platforms and maybe highlight some of the problem rows, we are happy to take a look. Unfortunately, XL2BB does not seem to transfer some of these irregular characters in their original form.

Thanks @Alex Blakenburg. There seems to be only one line with that issue so for now everything looks good.

 

[Alex Blakenburg]

Thanks for letting us know. Glad its working for you.