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
Cheers, and thanks again.
Chris