Calling a module from another module

[Scott Bass]

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

 

[Chip Pearson]

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, LLC
www.cpearson.com

 

[Scott Bass]

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

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

 

[Scott Bass]

On Feb 25, 8:20 am, Chip Pearson <[email protected]> wrote:

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?

I've found a weirdness with the ResetLastCell macro. I've been
manually executing it on each sheet. As I've done so, and re-saved
the .xls file, I've noticed the filesize bloating. It's gone from 3MB
to 54MB! Resaving it as an .xlsx file (we've got the Excel 2007
converters, I'm using Excel 2003) shrinks it to 314KB, so I'm hoping I
can shrink the size of the file using that approach.

Scott

 

[Scott Bass]

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

 

[Bob Phillips]

For Each sh In ActiveWorkbook.Worksheets

Call ResetLastCell(sh)
Next sh

HTH

Bob