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

for starters change this typo:

lLastComn change to: lLastColumn

RBS

"Chet" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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
>
>