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