Clearing Empty Space in Excel 2

[AlexCuse]

Hi All,

I have a file that is used as a destination for bad records discovered while running a SQL Server 2000 DTS Package. This file seems to be suffering some kind of bloating (my hunch is that it has some kind of formatting attached to all cells). I have tried deleting empty cells, and the size of the (empty!) file is still about 13 megs!

Is there any code that I can use to clear all data from the spreadsheet except for the first row of each tab, and also remove any formatting (while keeping formatting in the first row)?

I would prefer to add this code to an access module that handles placing the file in the correct destination each month, but even something that I can run manually when the file gets too large would be great.

Thanks a lot,

Alex

It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[AnotherHiggins]

Are there any modules or named ranges in the workbook?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[AlexCuse]

Yes, each tab is named something like XXX_APP (for the name of the import file the records come from)

Thanks for taking the time to read this,

Alex

It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[PHV]

A starting point:
For Each ws In xlApp.ActiveWorkbook.Worksheets
ws.Range("A2", ws.Range("A2").SpecialCells(11)).EntireRow.Delete ' 11=xlLastCell
ws.UsedRange
Next


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[AlexCuse]

Hi PH,

Thanks for your reply.

I am having a little trouble getting this to work. I'm getting run-time error 91 no matter how I try to define the WS (when running through excel). I am more accustomed to Access VBA, and because of this I feel like I might be missing some of the nuances required by Excel.

I have been trying to use this code through Excel primarily, but have even tried a few things in Access to utilize it. Any further advice would be greatly appreciated.

Thanks,

Alex

PS this is how I am defining ws

ws = xlApp.ActiveWorkbook.ActiveSheet



It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[AnotherHiggins]

Try PH's code as it is posted.

Sub MacroName
For Each ws In xlApp.ActiveWorkbook.Worksheets
ws.Range("A2", ws.Range("A2").SpecialCells(11)).EntireRow.Delete ' 11=xlLastCell
ws.UsedRange
Next
End Sub

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[AnotherHiggins]

BTW, have a look at 'Named Ranges' in Excel's help file. A sheet name is not a named range.

To look for Named Ranges, go to Insert > Name > Define. Any Named Ranges will be listed.

To look for modules, while the problem workbook is open press [Alt]+[F11]. To ensure that Project Explorer is open, press [Crtl]+[R] See if the Project (your workbook name) has any modules associated with it.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[AlexCuse]

I get object required error (424). I think it might be for ws, but I am not sure anymore. Let me know what you think.

Thanks,

Alex


It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[AnotherHiggins]

D'oh!

That's what I get for not actually looking[/i] at the code.

Try it without the "xlApp."

Sub MacroName()
For Each ws In ActiveWorkbook.Worksheets
  ws.Range("A2", ws.Range("A2").SpecialCells(11)).EntireRow.Delete ' 11=xlLastCell
  ws.UsedRange
Next
End Sub

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[AlexCuse]

That was (kind of) simple. Got my template file down from 13 megs (before adding data) to 61k! Thanks for your help, I was getting so frustrated with this (no matter how many times I would go in and delete all rows manually, the file would not shrink), and I was even considering giving our clients an access database with the error files in it rather than an excel file.

I know it must be frustrating dealing with a rookie like me on this stuff. You guys are awesome.

Alex


It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[PHV]

Alex, I gave you a starting point in AccessVBA assuming you already had an Excel.Application object I named xlApp.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[AlexCuse]

I started thinking that, but when trying in Access I was unable to get around the object problems that seemingly came from my use of 'WS'. I tried defining ws as excel.worksheet, but that led to problems with my use of ws.UsedRange.

Here is my sub,

Sub Clearout()

Dim xlApp As Excel.Application
Dim ws As Excel.Worksheet

xlApp.Workbooks.Open "c:\test.xls"

For Each ws In xlApp.ActiveWorkbook.Worksheets
  ws.Range("A2", ws.Range("A2").SpecialCells(11)).EntireRow.Delete ' 11=xlLastCell
  ws.UsedRange
  
Next


End Sub

Any insight you have on this would be very helpful.

Thanks,

Alex



It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[PHV]

I'd use late binding to avoid office's versions issues:
Sub Clearout()
Dim xlApp As Object
Dim ws
Set xlApp = CreateObject("Excel.Application")
xlApp.Workbooks.Open "c:\test.xls"
For Each ws In xlApp.ActiveWorkbook.Worksheets
ws.Range("A2", ws.Range("A2").SpecialCells(11)).EntireRow.Delete ' 11=xlLastCell
ws.UsedRange
Next
Set ws = Nothing
xlApp.ActiveWorkbook.Save
xlApp.Quit
Set xlApp = Nothing
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[AlexCuse]

Thanks PH, you're the best!

It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[firefytr]

If the application is already running you'll create another (un-needed) instance. Best to check if you already have one running and test if workbooks are open as well...

Sub Clearout()
    Dim xlApp As Object, wb As Object, ws As Object
    Dim blnExcelCreated As Boolean, blnWbOpen As Boolean
    On Error Resume Next
    blnExcelCreated = False
    Set xlApp = GetObject(, "Excel.Application")
    If Err <> 0 Then
        Set xlApp = CreateObject("Excel.Application")
        On Error GoTo 0
        blnExcelCreated = True
    End If
    If WbOpen("test.xls") Then
        Set wb = xlApp.Workbooks("test.xls")
        blnWbOpen = True
    Else
        Set wb = xlApp.Workbooks.Open("c:\test.xls")
        blnWbOpen = False
    End If
    For Each ws In wb.Worksheets
        ws.Range("A2", ws.Range("A2").SpecialCells(11)).EntireRow.Delete    ' 11=xlLastCell
        ws.UsedRange
    Next
    wb.Save
    If blnWbOpen = False Then wb.Close
    If blnExcelCreated Then xlApp.Quit
End Sub

Function WbOpen(wbName As String) As Boolean
    On Error Resume Next
    WbOpen = Len(Workbooks(wbName).Name)
End Function

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci