Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling a module from another module
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calling a module from textbox | Excel Programming | |||
calling a module | Excel Discussion (Misc queries) | |||
calling another module from within module | Excel Programming | |||
Calling a function in another module | Excel Programming | |||
Calling worksheet module from other module. | Excel Programming |