EXCEL2003: non-empty empty cells

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15498
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

EXCEL2003: non-empty empty cells

Post by ChrisGreaves »

The attached spreadsheet appears to be void of data in columns A:D, except for the first row, which contains column headings.
Yet, when I use Ctrl-Home to return to cell A1 and then Ctrl-DownArrow, the cursor travels to Cell A173. From A174, Ctrl-DownArrow takes me to row 65336 as I would expect.

Code: Select all

Sub test()
    MsgBox Len(ActiveCell.Value)
End Sub
The MsgBox suggests to me that the cell is empty, yet from within Excel2003 I can detect no evidence of cell contents.

For example, loading a space into the cell makes it appear to be empty, but Len(ActiveCell.Value) then reports 1 rather than 0.

The file originated in an Android phone as an export of contacts to a VCF file ("20210301.vcf.xls")
Thanks for any suggestions.
Chris
You do not have the required permissions to view the files attached to this post.
An expensive day out: Wallet and Grimace

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: EXCEL2003: non-empty empty cells

Post by HansV »

This often happens when importing data from other sources. The cells have an invisible attribute that makes Excel treat them as non-empty: the formula

=ISBLANK(A173)

returns FALSE, even though the formula

=LEN(A173)

returns 0.

Simply select the empty rows, then delete (not clear) them. Save the workbook and the used range will shrink.
Best wishes,
Hans

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15498
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: EXCEL2003: non-empty empty cells

Post by ChrisGreaves »

HansV wrote:
29 Mar 2021, 16:04
This often happens when importing data from other sources. The cells have an invisible attribute that makes Excel treat them as non-empty...
Thanks Hans, I half-guessed that, with the data arriving via a vcf from a different operating system.

I came back here to add something else that I thought might have been the bug in the ointment: The cells are formatted as TEXT, and I tried setting the format back to General with no change in behaviour. (I have a distant memory of Ctrl-End traveling to the limits of previously formatted cells until one saved and reopened the worksheet, but that might have been DOS Lotus 2.1)
Simply select the empty rows, then delete (not clear) them. Save the workbook and the used range will shrink.
This I can do today, but not in the future. This application is supposed to run unattended as a utility might, cleaning up, we hope, thousands of workbooks for hundreds of users in dozens of cities ...

Right now my status reads "there is no way to detect these non-empty empty cells programmatically", that is, automatically.

Glumly
Chris
An expensive day out: Wallet and Grimace

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15498
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: EXCEL2003: non-empty empty cells

Post by ChrisGreaves »

HansV wrote:
29 Mar 2021, 16:04
...The cells have an invisible attribute that makes Excel treat them as non-empty: ...
There again, there is something odd in Excel in that it can detect the invisible attributes as evidenced by the behaviour of Ctrl-DownArrow, but will not let the programmer take action based on the existence of the evident but hidden attribute.
Rather selfish of Excel, I think.

Cheers (now that that is off my chest)
Chris
An expensive day out: Wallet and Grimace

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: EXCEL2003: non-empty empty cells

Post by HansV »

You could automate it. After opening the workbook, run

Code: Select all

Sub Cleanup()
    Dim r1 As Long
    Dim r2 As Long
    r1 = ActiveSheet.UsedRange.Rows.Count
    r2 = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    If r1 > r2 Then
        Range(r2 + 1 & ":" & r1).Delete
        ' Dummy reference to usedrange forces Excel to update it
        ActiveSheet.UsedRange
    End If
End Sub
Best wishes,
Hans

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15498
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: EXCEL2003: non-empty empty cells

Post by ChrisGreaves »

HansV wrote:
29 Mar 2021, 18:28
You could automate it. After opening the workbook, run

Code: Select all

Sub Cleanup()
...    If r1 > r2 Then
...End Sub
Thanks Hans; this almost works, except that in my workbook in Excel2003 the condition "r1 > r2" is never true on my problem columns.
That said, I am not too old to steal good code when it will serve me well.
I was part way through an awful macro when yours struck, so I have purloined the

Code: Select all

Range(r2 + 1 & ":" & r1).Delete
' Dummy reference to usedrange forces Excel to update it
ActiveSheet.UsedRange
and memorialized it thus:

Code: Select all

Sub Macro1()
    ActiveCell.SpecialCells(xlLastCell).Select
    Dim lngColumn As Long
    lngColumn = ActiveCell.Column
    Dim lngRow As Long
    For lngRow = ActiveCell.Row To 2 Step -1
        Dim rng As Range
        Set rng = Cells(lngRow, lngColumn)
        If IsEmpty(rng) Then
'        If IsNull(rng) Then
            ActiveSheet.Cells(lngRow, lngColumn).Select
            Stop
        Else
            ActiveSheet.Cells(lngRow, lngColumn).Select
            If Application.WorksheetFunction.IsNumber(rng) Then
                Stop
            Else
                If Application.WorksheetFunction.IsText(rng) Then
                    If Len(rng.Value) = 0 Then
                        rng.Delete
'                        Stop
                    Else
                        Stop
                    End If
                Else
                    Stop
                End If
            End If
        End If
    Next lngRow
End Sub
Now that your blood pressure is almost back to normal, this is a work-in-progress as I try to find out just what it is about these cells in this workbook.
The disabled IsNull has been sent off stage but is sulking in the wings.
Application.WorksheetFunction does not support IsBlank, which IsAPity.
The STOP statements are placeholders to alert me to a satisfied condition.

This particular workbook is ("VCF"!!!) a sheet of contact data from an Android phone, so for now, a function that finds no value (numeric or text) in a cell is probably going to be OK in declaring that the cell is empty/null/blank.

That is, if my code can't find any data to extract from the cell, then the cell is as good as empty.

The "hidden attribute" note can be lodged in a User Guide; noone reads them anyway :rtfm: :flee: :flee:

Cheers, and thanks again.
Chris
An expensive day out: Wallet and Grimace

jolivanes
Lounger
Posts: 27
Joined: 24 Nov 2015, 05:23

Re: EXCEL2003: non-empty empty cells

Post by jolivanes »

Code: Select all

Sub Maybe_Dangerously()
Dim i As Long
Application.ScreenUpdating = False
    For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
        If Len(Cells(i, 1)) + Len(Cells(i, 2)) + Len(Cells(i, 3)) + Len(Cells(i, 4)) = 0 Then
            Cells(i, 1).EntireRow.Delete
'                or
'            Cells(i, 1).Resize(, 4).Delete Shift:=xlUp
        End If
    Next i
Application.ScreenUpdating = True
End Sub

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15498
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: EXCEL2003: non-empty empty cells

Post by ChrisGreaves »

jolivanes wrote:
31 Mar 2021, 15:46

Code: Select all

If Len(Cells(i, 1)) + Len(Cells(i, 2)) + Len(Cells(i, 3)) + Len(Cells(i, 4)) = 0 ...
Hi jolivanes, and my apologies for taking a week to get back to you.
Have I understood this code?
Essentially you are saying "Take the cells four at a time (for efficiency) from the farthest column, and if their length is zero, all the way to the bottom, you can delete the column", right?
That is "test every cell" (until a truly non-empty cell is found)
Thanks
Chris
An expensive day out: Wallet and Grimace

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15498
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: EXCEL2003: non-empty empty cells

Post by ChrisGreaves »

ChrisGreaves wrote:
29 Mar 2021, 15:20
The attached spreadsheet appears to be void of data in columns A:D, except for the first row, which contains column headings. ...

Code: Select all

Public Sub DeleteBlankColumns_LeftHeading()
    Application.ScreenUpdating = False
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationManual
    Dim lng As Long
    For lng = lngFindLastColumn(ActiveSheet) To 2 Step -1
        Application.Caption = lng
        If Application.WorksheetFunction.CountA(ActiveSheet.Columns(lng).EntireColumn) = 0 Then ' the column is completely empty
                ActiveSheet.Columns(lng).EntireColumn.Select
                ActiveSheet.Columns(lng).EntireColumn.Delete
        Else
'            If Application.WorksheetFunction.CountA(ActiveSheet.Columns(lng).EntireColumn) = 1 Then ' we have text in no other rows so ...
            If blnMyEmpty(ActiveSheet.Columns(lng).EntireColumn) Then ' we have text in no other rows so ...
                ActiveSheet.Columns(lng).EntireColumn.Select
                ActiveSheet.Columns(lng).EntireColumn.Delete
            Else
            End If
        End If
    Next lng
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.Caption = ""
End Sub
Function blnMyEmpty(rngColumn As Range) As Boolean
    blnMyEmpty = True
    Dim lng As Long
    For lng = 2 To rngColumn.Cells.Count
        If Len(rngColumn.Cells(lng).Value) > 0 Then
            blnMyEmpty = False
        Else
        End If
    Next lng
'Sub TESTlngMyCountA()
'    Dim lng As Long
'    lng = lngFindLastColumn(ActiveSheet)
'    MsgBox blnMyEmpty(ActiveSheet.Columns(lng).EntireColumn)
'End Sub
End Function
For the record, my status is that I have four contacts files from various sources:- GoogleContacts.xls, 20210301.vcf.xls, MyContacts.csv, Collected Addresses.csv
I run the macro "DeleteBlankColumns_LeftHeading" whose job is to detect and delete all columns except the leftmost column where a column has nothing but a column heading in row 1.

I am interested in column1 because the user has loaded that column with data labels; those labels are sacred to the user.
I am interested in row 1 because the export code loads the first row with column headings such as
First Name; Last Name; Display Name; Nickname; Primary Email; Secondary Email; Screen Name; Work Phone; Home Phone; Fax Number; Pager Number; Mobile Number; Home Address; Home Address 2; Home City; Home State; Home ZipCode; Home Country; Work Address; Work Address 2; Work City; Work State; Work ZipCode; Work Country; Job Title; Department; Organization; Web Page 1; Web Page 2; Birth Year; Birth Month; Birth Day; Custom 1; Custom 2; Custom 3; Custom 4; Notes
Many of these columns at the rightmost extremity will be empty, "Custom 3" and "Custom 4" among them

It is these headings, void except for the column headings in row 1, that I want to delete.
Superfluous columns just confuse us in the rationalization of contact data.

The code is weakly tested and is NOT guaranteed.
It runs slowly, but we don't care since in theory we only run it once per contact file.
Thanks to all
Chris
An expensive day out: Wallet and Grimace

jolivanes
Lounger
Posts: 27
Joined: 24 Nov 2015, 05:23

Re: EXCEL2003: non-empty empty cells

Post by jolivanes »

In your attachment from your first post, the last used row is row 173 although the A2:D173 range looks to be empty
After you run the following, the last used row is row 1.
It just deletes the four cells in the row until it reaches a row with actual readable characters in it.

Code: Select all

Sub Maybe_Dangerously()
Dim i As Long
Application.ScreenUpdating = False
    For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
        If Len(Cells(i, 1)) + Len(Cells(i, 2)) + Len(Cells(i, 3)) + Len(Cells(i, 4)) = 0 Then
            Cells(i, 1).Resize(, 4).Delete Shift:=xlUp
        End If
    Next i
Application.ScreenUpdating = True
End Sub
Good chance that I misunderstood your requirements though.