PROBLEM: cannot accurately determine Last Row / Last Column on worksheet
using either of the two code sets below

CAUSE: worksheet is a "shell" into which new files are pasted each month.
They vary in both # of Rows and Columns each month. I delete rows and columns
of prior month as a first step in macro. (I cannot close and re-open the
worksheet - doesn't work either!)

WHAT MAKES ME CRAZY! The resulting #s will be prior month's total rows and
columns IF the prior month's file was larger. I've used the code sets below
but neither accurately determines the last Row / Last Column. I must be
missing something really basic (every pun intended!)
All advice will be gratefully appreciated.'




CODE SETS:
'
FinalRow = ActiveCell.SpecialCells(xlCellTypeLastCell).Row
FinalColumn = ActiveCell.SpecialCells(xlCellTypeLastCell).Column
'
'
FinalRow = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlRows).Row
'
FinalColumn = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column
'

Try these two statements instead...

FinalRow = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious).Row

FinalColumn = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column

--
Rick (MVP - Excel)


"KG Old Wolf" wrote in message
...
PROBLEM: cannot accurately determine Last Row / Last Column on worksheet
using either of the two code sets below

CAUSE: worksheet is a "shell" into which new files are pasted each month.
They vary in both # of Rows and Columns each month. I delete rows and
columns
of prior month as a first step in macro. (I cannot close and re-open the
worksheet - doesn't work either!)

WHAT MAKES ME CRAZY! The resulting #s will be prior month's total rows and
columns IF the prior month's file was larger. I've used the code sets
below
but neither accurately determines the last Row / Last Column. I must be
missing something really basic (every pun intended!)
All advice will be gratefully appreciated.'




CODE SETS:
'
FinalRow = ActiveCell.SpecialCells(xlCellTypeLastCell).Row
FinalColumn = ActiveCell.SpecialCells(xlCellTypeLastCell).Column
'
'
FinalRow = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlRows).Row
'
FinalColumn = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column
'

Hi

To reset last used row/column use 'ActiveSheet.UsedRange' before you
determine last row/column

ActiveSheet.UsedRange
FinalRow = ActiveCell.SpecialCells(xlCellTypeLastCell).Row
FinalColumn = ActiveCell.SpecialCells(xlCellTypeLastCell).Column

Regards,
Per

On 23 Sep., 21:39, KG Old Wolf
wrote:
PROBLEM: cannot accurately determine Last Row / Last Column on worksheet
using either of the two code sets below

CAUSE: worksheet is a "shell" into which new files are pasted each month.
They vary in both # of Rows and Columns each month. I delete rows and columns
of prior month as a first step in macro. *(I cannot close and re-open the
worksheet - doesn't work either!)

WHAT MAKES ME CRAZY! The resulting #s will be prior month's total rows and
columns IF the prior month's file was larger. I've used the code sets below
but neither accurately determines the last Row / Last Column. I must be
missing something really basic (every pun intended!)
All advice will be gratefully appreciated.'

CODE SETS:
'
FinalRow = ActiveCell.SpecialCells(xlCellTypeLastCell).Row
FinalColumn = ActiveCell.SpecialCells(xlCellTypeLastCell).Column
'
'
FinalRow = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlRows).Row
'
FinalColumn = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column
'

Guys -

thank for the effort but if you look back at the two code snippets I
provided, you will notice that they appear to be the same code as you each
suggested... and that doesn't work.

The problem is now fixed with the insertion of a preceeding line of simple

--- ActiveWorkbook.Save
FinalRow = ActiveCell.SpecialCells(xlCellTypeLastCell).Row
FinalColumn = ActiveCell.SpecialCells(xlCellTypeLastCell).Column

Thanks and I hope this solution helps others...
Ken





"KG Old Wolf" wrote:

PROBLEM: cannot accurately determine Last Row / Last Column on worksheet
using either of the two code sets below

CAUSE: worksheet is a "shell" into which new files are pasted each month.
They vary in both # of Rows and Columns each month. I delete rows and columns
of prior month as a first step in macro. (I cannot close and re-open the
worksheet - doesn't work either!)

WHAT MAKES ME CRAZY! The resulting #s will be prior month's total rows and
columns IF the prior month's file was larger. I've used the code sets below
but neither accurately determines the last Row / Last Column. I must be
missing something really basic (every pun intended!)
All advice will be gratefully appreciated.'




CODE SETS:
'
FinalRow = ActiveCell.SpecialCells(xlCellTypeLastCell).Row
FinalColumn = ActiveCell.SpecialCells(xlCellTypeLastCell).Column
'
'
FinalRow = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlRows).Row
'
FinalColumn = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column
'