Followup To Jim Thomlinson macro

[SteveDB1]

Hi Jim,
yesterday you'd provided a macro that compacts worksheets.
I have got it to work-- for most worksheets-- but there are a few that it
gets hung up on.
The problem that I had yesterday is that I was running it before I'd saved
the file to the new xlsx format, in 2007. Once I realized that it worked well
AFTER I saved to the xlsx format, it went well.
However, something has arisen that I do not understand, and cannot readily
identify. I've copied the code for the macro/UDF's below my discussion.
at the end of the 3rd function, I get an error at:
Set LastCell = wks.Cells(lngLastRow, intLastColumn)

WHEN I get the error, which so far has been infrequent, it happens there.
I see that the LastCell is a user defined function.
The error states that it's an "application defined, or object defined error."
I.e.,
LastCell = nothing
wks.cells(lngLastRow, intLastColumn) = <application defined, or object
defined error>
lngLastRow = 39
intLastColumn = 0

Would you please explain to me
1- why this would happen.
2- how to fix this from occurring in the future.
Thank you.
----------------------------------------------------------------------------
Sub CompactAllSheets()
Dim wks As Worksheet
'Dim lngVisible As Long

For Each wks In Worksheets
'lngVisible = wks.Visible
CompactSheet wks
'wks.Visible = lngVisible
Next wks

End Sub


Public Sub CompactSheet(Optional ByVal wks As Worksheet)
Dim rng As Range

If wks Is Nothing Then Set wks = ActiveSheet
Set rng = LastCell(wks)
wks.Range(rng.Offset(0, 1), wks.Cells(1,
Columns.Count)).EntireColumn.Delete
wks.Range(rng.Offset(1, 0), wks.Cells(Rows.Count, 1)).EntireRow.Delete

End Sub


Public Function LastCell(Optional ByVal wks As Worksheet) As Range
Dim lngLastRow As Long
Dim intLastColumn As Integer

If wks Is Nothing Then Set wks = ActiveSheet
On Error Resume Next
lngLastRow = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
intLastColumn = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
If lngLastRow = 0 Then
lngLastRow = 1
intLastColumn = 1
End If
Set LastCell = wks.Cells(lngLastRow, intLastColumn)

End Functio
--------------------------------------------------------------------------------

 

[Jim Thomlinson]

I have been using it forever and never had a problem. The issue you are
running into is the intLastColumn = 0. The last column must be 1 or greater.
How you end up with 0 is a mystery that may be difficult to debug...

 

[SteveDB1]

Hi again,
Thanks for your response.
I believe you. Once I figured out the save as xlsx issue I experienced, and
found/modified a macro to auto-operate all of this, it processed 32 files in
7 minutes, and only failed with the 33rd file. So, in my mind it goes without
saying that this is an awesome routine you've provided, and I am extremely
grateful.
Ok.... difficult to debug.
Do you have ideas that might give me a direction to start?
What kinds of things would cause that variable to go to zero? I saw that you
set the lngLastRow = 1, if it was zero.
If lngLastRow = 0 Then
lngLastRow = 1
intLastColumn = 1
End If
would I need to do something identical with the intLastColumn too?
I.e.,
If intLastColumn = 0 Then
lngLastRow = 1
intLastColumn = 1
End If

I looked at the last file I worked, and there were no worksheets that were
blank, or had only one column with data. I.e., all of the worksheets had
plenty of data on them. So, I do not understand why intLastColumn would go to
0.

As stated before, there are only three files out of the 40 or 50 files
processed so far, that have failed, with this specific error.

 

[SteveDB1]

Hi Jim,
Well, I'm back again.
I tried the fix I referenced last night, and the file I'd had the "LastCell"
issue with was processed with no trouble.
As such, I set about running the entire routine, and now I've found another
issue. Code below, with the errors beneath that.
------------------------------------------------
Public Sub CompactSheet(Optional ByVal wks As Worksheet)
Dim rng As Range

If wks Is Nothing Then Set wks = ActiveSheet
Set rng = LastCell(wks)
wks.Range(rng.Offset(0, 1), wks.Cells(1,
Columns.count)).EntireColumn.Delete
wks.Range(rng.Offset(1, 0), wks.Cells(Rows.count, 1)).EntireRow.Delete
-----------------------------------------------
The error that I get is the same as before-- application defined, or object
defined error with this line.

wks.Range(rng.Offset(1, 0), wks.Cells(Rows.count, 1)).EntireRow.Delete

The error appears to be located in the rng.offset(1,0) portion. I.e., the
wks.Cells(Rows.count,1) shows the 65536th row, which'd be at the bottom of
the worksheet for the old format.
So, my question here... what would cause this type of an error?
How would I go about resolving it?
Any ideas as to why this would result in an error?
Could this be due to the limitation of the old format, and once saved into
the new format, needs to be closed, then re-opened to accurately obtain the
data?
Perhaps I'm not saying this the way it needs to be said, but I have found in
times past, and someone once pointed out that importing a new format
worksheet into a newly converted book, from the old format-- xlsx--> (xls
---->xlsx), without first closing the newly converted book, will cause an
error which generally states that the workbook you're importing this
worksheet into does not contain as many rows, and columns, as the source.
Well, I just tried my theory, and it still failed, with the same error.
Any thoughts, or ideas would be appreciated.
Best.
Thank you again for your helps.

 

[SteveDB1]

in all fairness, I need to say this....
I had previously processed this file manually-- the very first one-- and had
forgotten that there was actually 65536 rows of actual data in a single
column, on approx. 10 worksheets. Sadly, this was one that needed to be done
manually, as the data was useless fill that someone had inadvertantly
placed/filled down.
So, for this particular file, and post 4, it was resolved by me remembering
it needed to be done manually-- sigh.....