On Feb 25, 8:20*am, Chip Pearson wrote:
What version Excel are you using? *If you are using 2007 or later, the
code

With Range(Cells(1, lastcell.Column + 1), "IV65536")

isn't right because there are more columns past IV and more rows past
65536.

Try

With Range(Cells(1, lastcell.Column + 1), *_
* * * * Cells(Cells.Count))

this will handle the worksheet in any version of Excel.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
* * * * Excel, 1998 - 2010
Pearson Software Consulting, LLCwww.cpearson.com

On Wed, 24 Feb 2010 12:25:53 -0800 (PST), Scott Bass

wrote:
Hi,

I found this macro to reset the last cell from an Excel forum:

Sub ResetLastCell()

* 'http://support.microsoft.com/default...&Product=xlw2K
* ' Save the lastcell and start there.
* Set lastcell = Cells.SpecialCells(xlLastCell)
* ' Set the rowstep and column steps so that it can move toward
* ' cell A1.
* rowstep = -1
* colstep = -1
* ' Loop while it can still move.
* While (rowstep + colstep < 0) And (lastcell.Address < "$A$1")
* * *' Test to see if the current column has any data in any cells.
* * *If Application _
* * * * * *.CountA(Range(Cells(1, lastcell.Column), lastcell)) _
* * * * * * 0 Then colstep = 0 *'If data then stop the stepping
* * * * ' Test to see if the current row has any data in any cells.
* * * * ' If data exists, stop row stepping.
* * * * If Application _
* * * * * * * .CountA(Range(Cells(lastcell.Row, 1), lastcell)) _
* * * * * * * 0 Then rowstep = 0
* * * * * *' Move the lastcell pointer to a new location.
* * * * * *Set lastcell = lastcell.Offset(rowstep, colstep)
* * * * * *' Update the status bar with the new "actual" last cell
* * * * * *' location.
* * * * * *Application.StatusBar = "Lastcell: " & lastcell.Address
* Wend
* ' Clear and delete the "unused" columns.
* With Range(Cells(1, lastcell.Column + 1), "IV65536")
* * *Application.StatusBar = "Deleting column range: " & _
* * * * .Address
* * *.Clear
* * *.Delete
* End With
* ' Clear and delete the "unused" rows.
* With Rows(lastcell.Row + 1 & ":65536")
* * *Application.StatusBar = "Deleting Row Range: " & _
* * * * .Address
* * *.Clear
* * *.Delete
* End With
* ' Select cell A1.
* Range("a1").Select
* ' Reset the status bar to the Microsoft Excel default.
* Application.StatusBar = False
End Sub

And I have this macro to save all worksheets as a CSV when I save the
workbook:

Sub SaveAllAsCSV()

On Error GoTo errHandler

Dim ThisPath As String
Dim Sheet As Worksheet
Dim FileName As String

With Application
* *.DisplayAlerts = False
* *.EnableEvents = False
* *.ScreenUpdating = False
End With

'Since you're in the workbook module, no workbook reference is
required when referring to this workbook
For Each Sheet In Sheets

* *ThisPath = Path 'same here
* *FileName = ThisPath & "\" & Sheet.Name & ".csv"

* *Sheet.Copy
* *With ActiveWorkbook
* * * *.SaveAs FileName:=FileName, FileFormat:=xlCSV
* * * *.Close 'I took the liberty of closing the newly created csv
files
* *End With
Next

Cleanup:
With Application
* *.DisplayAlerts = True
* *.EnableEvents = True
* *.ScreenUpdating = True
End With

Exit Sub

errHandler:
* *MsgBox Err.Source & " " & _
* *Err.Number & " " & _
* *Err.Description
* *GoTo Cleanup
End Sub

I need to execute ResetLastCell for every worksheet in the workbook.
I tried this but it doesn't work:

Sub ResetAllLastCell()

On Error GoTo errHandler

Dim Sheet As Worksheet

With Application
* *.DisplayAlerts = False
* *.EnableEvents = False
* *.ScreenUpdating = False
End With

'Since you're in the workbook module, no workbook reference is
required when referring to this workbook
For Each Sheet In Sheets
* *Sheet.Activate <<<<<<<<<<<<<<<<<<<<
* *ResetLastCell <<<<<<<<<<<<<<<<<<<< *doesn't work
Next

Cleanup:
With Application
* *.DisplayAlerts = True
* *.EnableEvents = True
* *.ScreenUpdating = True
End With

Exit Sub

errHandler:
* *MsgBox Err.Source & " " & _
* *Err.Number & " " & _
* *Err.Description
* *GoTo Cleanup
End Sub

Can you please advise the correct syntax?

(Note: *The reason I need to do this is I'm actually reading the Excel
data into another application (SAS) and right now I'm getting 64K
mostly empty rows in the SAS tables. *The large number of rows read in
is also affecting the import performance into SAS. *I wish Excel, or
at least the Jet API, was "smarter" at knowing when the end of data
occurs.)

Thanks,
Scott

Hi Chip,

Thanks, much appreciated. Follow up questions:

1) Is the ResetLastCell macro best practice? It does take "a while"
to run (say 60-90 seconds on my machine). Is there a better approach?

2) I've got both Excel 2003 and 2007 on my machine and use both. I'm
aware that Excel 2007 expands the number of columns and rows over
Excel 2003. However, wouldn't your change cause a performance
decrease in the macro - admittedly at the gain of correct behavior in
Excel 2007. I'm not working with any worksheets with data greater
than can be contained in Excel 2003.

3) If anyone can comment on how to call ResetLastCell for all
worksheets in a workbook that would be great.

Thanks,
Scott