VBA - Why does my CurrentRegion include blank columns to the right of my data

[Harry Flashman]

I have a workbook that is generated by an online portal. When I try to find the last cell in the data I notice that the column is actually blank yet Excel is acting as if it must contain data.

Thus Range("A1").CurrentRegion includes two columns to the right of my data that are completely blank.

Likewise if I write something like Range("A1").End(xlToRight) the cell returned is completely blank (it is actually two cells to the right of my data table). Or if I write Cells(1, Rows(1).Cells.Count).End(xlToLeft), I get the same blank cell, that is actually two columns to the right of my data.

Can anyone suggest what might be going on with these cells? Why does Excel act as if these cells contain data?

 

[MARK858]

You probably have brought in some non-printing characters.
On a copy of your sheet see if you run the code below it clears the issue.

Sub Trimit()
    Dim myCell As Range, myRng As Range
    
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With

    Set myRng = ActiveSheet.UsedRange
    
    With myRng
        .Replace What:=Chr(160), Replacement:=Chr(32), LookAt:=xlPart
        .Replace What:=Chr(13) & Chr(10), Replacement:=Chr(32), LookAt:=xlPart
        .Replace What:=Chr(13), Replacement:=Chr(32), LookAt:=xlPart
        .Replace What:=Chr(21), Replacement:=Chr(32), LookAt:=xlPart
        .Replace What:=Chr(8), Replacement:=Chr(32), LookAt:=xlPart
        .Replace What:=Chr(9), Replacement:=Chr(32), LookAt:=xlPart
    End With

    On Error Resume Next
    For Each myCell In Intersect(myRng, _
                               myRng.SpecialCells(xlConstants, xlTextValues))
        myCell.Value = Application.Trim(myCell.Value)
    Next myCell
    On Error GoTo 0

    With Application
        .Calculation = xlCalculationAutomatic
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
End Sub

 

[Harry Flashman]

Thanks. That was the problem: non printing characters (no idea what they are).

 

[MARK858]

non printing characters (no idea what they are).

They are things like carriage returns, line feeds etc. and are quite common when importing/downloading documents.

 

[Harry Flashman]

Of course. I think used to know that, but I had completely forgotten. Once again, thanks for your help.