Error: Obj variable or With block variable not set
 

Keep getting an error msg saying "object variable or With block
variable not set" and I'm not sure how to fix it. My code is from a
book Excel 2002 VBA A Programmers Reference by Stephen Bullen,etc al.
Any idea on eliminating the error message? I put X's at the line where
the error msg happens. The code is supposed to eliminate any rows or
columns that only have formatting and nothing else and reset the
LastCell.

Thanks,
Chet

Dim lLastRow As Long
Dim lRealLastRow As Long
Dim lLastColumn As Long
Dim lRealLastColumn As Long

With Range("A1").SpecialCells(xlCellTypeLastCell)
lLastRow = .Row
lLastColumn = .Column
End With

lRealLastRow = _
Cells.Find("*", Range("A1"), xlFormulas, , xlByRows,
xlPrevious).Row

'XXXXXXXXXXXXXXXXXERROR HAPPENING HEREXXXXXXXXXXXXXXXXXX BELOWXXXXXX
lRealLastColumn = _
Cells.Find("*", Range("A1"), xlFormulas, , xlByColumns,
xlPrevious).Column

If lRealLastRow < lLastRow Then
Range(Cells(lRealLastRow + 1, 1), Cells(lLastRow,
1)).EntireRow.Delete
End If
If lRealLastColumn < lLastComn Then
Range(Cells(1, lRealLastColumn + 1), _
Cells(1,
lLastColumn)).entercolumn.Delete
End If

ActiveSheet.UsedRange 'resets LastCell

Error: Obj variable or With block variable not set
 

for starters change this typo:

lLastComn change to: lLastColumn

RBS

"Chet" wrote in message
oups.com...
Keep getting an error msg saying "object variable or With block
variable not set" and I'm not sure how to fix it. My code is from a
book Excel 2002 VBA A Programmers Reference by Stephen Bullen,etc al.
Any idea on eliminating the error message? I put X's at the line where
the error msg happens. The code is supposed to eliminate any rows or
columns that only have formatting and nothing else and reset the
LastCell.

Thanks,
Chet

Dim lLastRow As Long
Dim lRealLastRow As Long
Dim lLastColumn As Long
Dim lRealLastColumn As Long

With Range("A1").SpecialCells(xlCellTypeLastCell)
lLastRow = .Row
lLastColumn = .Column
End With

lRealLastRow = _
Cells.Find("*", Range("A1"), xlFormulas, , xlByRows,
xlPrevious).Row

'XXXXXXXXXXXXXXXXXERROR HAPPENING HEREXXXXXXXXXXXXXXXXXX BELOWXXXXXX
lRealLastColumn = _
Cells.Find("*", Range("A1"), xlFormulas, , xlByColumns,
xlPrevious).Column

If lRealLastRow < lLastRow Then
Range(Cells(lRealLastRow + 1, 1), Cells(lLastRow,
1)).EntireRow.Delete
End If
If lRealLastColumn < lLastComn Then
Range(Cells(1, lRealLastColumn + 1), _
Cells(1,
lLastColumn)).entercolumn.Delete
End If

ActiveSheet.UsedRange 'resets LastCell

Error: Obj variable or With block variable not set
 

If Find doesn't find a value then there will be no range object from which
to get the "column" property.

You should either wrap that code in "on error resume next/on error goto 0"
or check to see if Find was successful before getting the column number.

Tim

--
Tim Williams
Palo Alto, CA


"Chet" wrote in message
oups.com...
Keep getting an error msg saying "object variable or With block
variable not set" and I'm not sure how to fix it. My code is from a
book Excel 2002 VBA A Programmers Reference by Stephen Bullen,etc al.
Any idea on eliminating the error message? I put X's at the line where
the error msg happens. The code is supposed to eliminate any rows or
columns that only have formatting and nothing else and reset the
LastCell.

Thanks,
Chet

Dim lLastRow As Long
Dim lRealLastRow As Long
Dim lLastColumn As Long
Dim lRealLastColumn As Long

With Range("A1").SpecialCells(xlCellTypeLastCell)
lLastRow = .Row
lLastColumn = .Column
End With

lRealLastRow = _
Cells.Find("*", Range("A1"), xlFormulas, , xlByRows,
xlPrevious).Row

'XXXXXXXXXXXXXXXXXERROR HAPPENING HEREXXXXXXXXXXXXXXXXXX BELOWXXXXXX
lRealLastColumn = _
Cells.Find("*", Range("A1"), xlFormulas, , xlByColumns,
xlPrevious).Column

If lRealLastRow < lLastRow Then
Range(Cells(lRealLastRow + 1, 1), Cells(lLastRow,
1)).EntireRow.Delete
End If
If lRealLastColumn < lLastComn Then
Range(Cells(1, lRealLastColumn + 1), _
Cells(1,
lLastColumn)).entercolumn.Delete
End If

ActiveSheet.UsedRange 'resets LastCell

Error: Obj variable or With block variable not set
 

At the very top of the module add the words "option explicit" (this will
require variable declarations) and then select Debug - Compile and fix the
errors that get pointed out. There are a bunch of typo's

Here is a link about Varaible declarations and option explicit...

http://www.cpearson.com/excel/DeclaringVariables.htm
--
HTH...

Jim Thomlinson


"Chet" wrote:

Keep getting an error msg saying "object variable or With block
variable not set" and I'm not sure how to fix it. My code is from a
book Excel 2002 VBA A Programmers Reference by Stephen Bullen,etc al.
Any idea on eliminating the error message? I put X's at the line where
the error msg happens. The code is supposed to eliminate any rows or
columns that only have formatting and nothing else and reset the
LastCell.

Thanks,
Chet

Dim lLastRow As Long
Dim lRealLastRow As Long
Dim lLastColumn As Long
Dim lRealLastColumn As Long

With Range("A1").SpecialCells(xlCellTypeLastCell)
lLastRow = .Row
lLastColumn = .Column
End With

lRealLastRow = _
Cells.Find("*", Range("A1"), xlFormulas, , xlByRows,
xlPrevious).Row

'XXXXXXXXXXXXXXXXXERROR HAPPENING HEREXXXXXXXXXXXXXXXXXX BELOWXXXXXX
lRealLastColumn = _
Cells.Find("*", Range("A1"), xlFormulas, , xlByColumns,
xlPrevious).Column

If lRealLastRow < lLastRow Then
Range(Cells(lRealLastRow + 1, 1), Cells(lLastRow,
1)).EntireRow.Delete
End If
If lRealLastColumn < lLastComn Then
Range(Cells(1, lRealLastColumn + 1), _
Cells(1,
lLastColumn)).entercolumn.Delete
End If

ActiveSheet.UsedRange 'resets LastCell