Save As (VBA) stopped working

[SueBK]

I have code in a macro to prompt the user to save the file, followed by a message box to say the task is complete.

    MsgBox ("Please save the report")
    file_name = Application.GetSaveAsFilename(FileFilter:="Microsoft Excel file (*.xlsx), *.xlsx")
    If file_name <> False Then
    ActiveWorkbook.SaveAs Filename:=file_name
    MsgBox "File Saved!"
    End If

    MsgBox ("All worksheets have now been created. Thank you.")

It has worked beautifully to date.

Today!!!! I get the "Please save the report" message, and then straight away the "all complete" message box and the whole *&^#@) thing hangs for ages. THEN, I can edit and make changes to my UNSAVED file, but I can't access the Home menu to save the dang thing and eventually it just falls over.

 

[Domenic]

Try specifying the file format...

ActiveWorkbook.SaveAs Filename:=file_name[COLOR=#ff0000], [/COLOR][COLOR=#ff0000]FileFormat:=51[/COLOR] 'xlOpenXMLWorkbook

Hope this helps!

 

[SueBK]

It seems that when I copy the 'entire column' across to new worksheets (I create five or six worksheets filtered by rows and columns from a mainsheet) the blank rows below my data are blowing my file size out. A 500kB main data file becomes a 50,000 kb file. If I manually delete everything from the end of the data to the bottom of the worksheet, the file size drops back and everything runs smooth. But I'm having some difficulty programming the deletion in VBA.

 

[MARK858]

If I manually delete everything from the end of the data to the bottom of the worksheet, the file size drops back and everything runs smooth. But I'm having some difficulty programming the deletion in VBA.

Try

Sub ResetRng()

    Dim x As Long
    Dim LastRow As Long, LastCol As Long

    Application.ScreenUpdating = False


    LastRow = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, _
                         LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    LastCol = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, _
                         LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    '        Range(Cells(1, LastCol + 1), Cells(65536, 256)).Delete    '97-2003
    '        Range(Cells(LastRow + 1, 1), Cells(65536, 256)).Delete    '97-2003
    Range(Cells(1, LastCol + 1), Cells(1048576, 16384)).Delete    '2007/10
    Range(Cells(LastRow + 1, 1), Cells(1048576, 16384)).Delete    '2007/10

Application.ScreenUpdating = True
End Sub

 

[SueBK]

Can you tell me what "long" is? I'm still not sure what 'dim' is, but I sort of know what it does. I dim ranges and strings, but I don't know what a long is.

What is it searching for; what does the * mean in the search request?

Will this work if I only have one row of data? On one sheet in particular I often have no records, so the sheet ends up with just the header row - which the client wants. I guess it shows we didn't forget to create the report. I had code (sorry, not at work, so I can't access it), but on that sheet it threw errors at me, and then deleted my header row.

Thank you for the code. I'll test it tomorrow.

 

[MARK858]

Can you tell me what "long" is? I'm still not sure what 'dim' is, but I sort of know what it does. I dim ranges and strings, but I don't know what a long is.

see the link below as it explains the use of variables and the types pretty well

Excel VBA Variables. Using Variables in Excel VBA Macro Code

What is it searching for; what does the * mean in the search request?

* is a wildcard and so it is searching for any value in a cell

Will this work if I only have one row of data?

It wouldn't error if you had 1 line but would if there were no lines.
The code below should loop through all the sheets, test if there is something in Range A1 on each sheet and if there is then run the code or if you wanted to you could use an error handler (I prefer to test a cell).

[color=darkblue]Sub[/color] ResetRng3()

    [color=darkblue]Dim[/color] x [color=darkblue]As[/color] [color=darkblue]Long[/color], LastRow [color=darkblue]As[/color] [color=darkblue]Long[/color], LastCol [color=darkblue]As[/color] [color=darkblue]Long[/color]

    Application.ScreenUpdating = [color=darkblue]False[/color]

    [color=darkblue]For[/color] x = 1 [color=darkblue]To[/color] Sheets.Count
        [color=darkblue]With[/color] Sheets(x)
            [color=darkblue]If[/color] Len(.Range("A1")) > 0 [color=darkblue]Then[/color]
                LastRow = .Cells.Find(What:="*", After:=.Range("A1"), LookIn:=xlValues, _
                                      LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                LastCol = .Cells.Find(What:="*", After:=.Range("A1"), LookIn:=xlValues, _
                                      LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
                .Range(.Cells(1, LastCol + 1), .Cells(65536, 256)).Delete    [color=green]'97-2003[/color]
                .Range(.Cells(LastRow + 1, 1), .Cells(65536, 256)).Delete    '97-2003
                [color=green]'.Range(.Cells(1, LastCol + 1), .Cells(1048576, 16384)).Delete '2007/10[/color]
                [color=green]'.Range(.Cells(LastRow + 1, 1), .Cells(1048576, 16384)).Delete '2007/10[/color]

            [color=darkblue]End[/color] [color=darkblue]If[/color]
        [color=darkblue]End[/color] [color=darkblue]With[/color]
    [color=darkblue]Next[/color] x


    Application.ScreenUpdating = [color=darkblue]True[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[SueBK]

Thank you so much for the first code! Ran a report this morning without the code - 45,000kb; ran the code - 9kb. I think I'll just add it to the bottom of all my 'create a filtered worksheet' macros.

Question: I have a master sheet of 100+ columns, up to 3,000 rows. I create filtered reports consisting of a subset of columns and filtered rows. My current code pattern is:

- apply filter to master data set
- copy selected columns to new worksheet

I notice on sheets that have no filter the columns copying happens a lot faster. I'm wondering if I would be better off reversing my code:
- copy selected columns to new worksheet
- apply filter
- delete hidden rows
(OR, apply reverse filter, delete visible rows - which would require rewriting the filters, which I'm not sure I want to do in most cases. In some cases, that would actually be easier.)

 

[MARK858]

I notice on sheets that have no filter the columns copying happens a lot faster. I'm wondering if I would be better off reversing my code:
- copy selected columns to new worksheet
- apply filter
- delete hidden rows

You will probably find that the time taken deleting the hidden rows will outweigh the time saved on the copy.
How are you doing the copy i.e. are you using Copy-Paste or Copy-Destination or something else (if you are not sure then post a section of your relevant code)?

 

[SueBK]

I'm at home now and have a long weekend (bring it on!). From memory my code is 'find this heading, copy the entire column, paste in this sheet one over from the last heading'. It's beautiful looping/repeating code that some helpful soul on here gave me.

I did change around the copy and filter order. AND I changed all my set ranges for filtering and sorting to 'UsedRange'. My range size changes, mainly rows, but potentially columns. I didn't know how to set it as a variable when I put this together, so it was hard-wired with quite a large cell range.

I ran the reports today, with all these new factors (empty row deletion, re-ordered activity, UsedRange) and they ran a lot faster and a lot smaller. I'm very happy.

My next learning curve is error messages. I have no error stops or whatever they're called, because I'm the only that's been using my code and I just debug. Now, they want to give others access to running the reports, so I need to learn how to stop code that's got a fault for whatever reason.

 

[MARK858]

UsedRange you should be careful of, it has a nasty habit of retaining a memory.
you are probably safer (going by your current issue) using something like the function below (used like in the sub).

Public Function myUsedRange() As Range
    Dim LastRow As Long, LastCol As Long

    With ActiveSheet
If WorksheetFunction.CountA(.Cells) = 0 Then
Set myUsedRange = Nothing
Else
        LastRow = .Cells.Find(What:="*", After:=.Range("A1"), LookIn:=xlValues, _
                              LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        LastCol = .Cells.Find(What:="*", After:=.Range("A1"), LookIn:=xlValues, _
                              LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

        Set myUsedRange = .Range(.Cells(1, "A"), .Cells(LastRow, LastCol))
        End If
    End With

End Function

Sub FindUsedRange()

    Dim Rng1 As Range

    Set Rng1 = myUsedRange
    If Rng1 Is Nothing Then
        MsgBox "There is no used range, the worksheet is empty."
    Else
        MsgBox "The real used range is: " & Rng1.Address
    End If

End Sub

For some basics on error checking see the link below

Error Handling In VBA