Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: XLSTART macro opens two ghost processes

  1. #1
    Board Regular
    Join Date
    Jul 2009
    Posts
    595
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default XLSTART macro opens two ghost processes

    Code:
    Sub col()
    ' col Macro
    '
    
    
    
    
    '
        With Selection.Font
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = 0
        End With
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 5420131
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    End Sub
    
    
    
    
    
    
    
    
    
    
    
    
    Sub fmat1()
    '
    ' fmat1 Macro
    '
    
    
    
    
    '
    
    
    
    
        Selection.NumberFormat = "#,##0;[Red](#,##0);-"
    End Sub
    
    
    
    
    Sub fmat2()
    '
    ' fmat1 Macro
    '
    
    
    
    
    '
    
    
    
    
        Selection.NumberFormat = "#,##0,;[Red](#,##0,);-"
    End Sub
    
    
    
    
    
    
    
    
    
    
    
    
    Sub ExcelDiet()
         
        Dim j               As Long
        Dim k               As Long
        Dim LastRow         As Long
        Dim LastCol         As Long
        Dim ColFormula      As Range
        Dim RowFormula      As Range
        Dim ColValue        As Range
        Dim RowValue        As Range
        Dim Shp             As Shape
        Dim ws              As Worksheet
         
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
         
        On Error Resume Next
         
        For Each ws In Worksheets
            With ws
                 'Find the last used cell with a formula and value
                 'Search by Columns and Rows
                On Error Resume Next
                Set ColFormula = .Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlFormulas, _
                LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
                Set ColValue = .Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, _
                LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
                Set RowFormula = .Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlFormulas, _
                LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
                Set RowValue = .Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, _
                LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
                On Error GoTo 0
                 
                 'Determine the last column
                If ColFormula Is Nothing Then
                    LastCol = 0
                Else
                    LastCol = ColFormula.Column
                End If
                If Not ColValue Is Nothing Then
                    LastCol = Application.WorksheetFunction.Max(LastCol, ColValue.Column)
                End If
                 
                 'Determine the last row
                If RowFormula Is Nothing Then
                    LastRow = 0
                Else
                    LastRow = RowFormula.Row
                End If
                If Not RowValue Is Nothing Then
                    LastRow = Application.WorksheetFunction.Max(LastRow, RowValue.Row)
                End If
                 
                 'Determine if any shapes are beyond the last row and last column
                For Each Shp In .Shapes
                    j = 0
                    k = 0
                    On Error Resume Next
                    j = Shp.TopLeftCell.Row
                    k = Shp.TopLeftCell.Column
                    On Error GoTo 0
                    If j > 0 And k > 0 Then
                        Do Until .Cells(j, k).Top > Shp.Top + Shp.Height
                            j = j + 1
                        Loop
                        If j > LastRow Then
                            LastRow = j
                        End If
                        Do Until .Cells(j, k).Left > Shp.Left + Shp.Width
                            k = k + 1
                        Loop
                        If k > LastCol Then
                            LastCol = k
                        End If
                    End If
                Next
                 
                .Range(.Cells(1, LastCol + 1), .Cells(.Rows.Count, .Columns.Count)).EntireColumn.Delete
                .Range("A" & LastRow + 1 & ":A" & .Rows.Count).EntireRow.Delete
            End With
        Next
         
        Application.ScreenUpdating = True
        Application.DisplayAlerts = True
         
    End Sub

    Thanks

  2. #2
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    8,472
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: XLSTART macro opens two ghost processes

    Is there a question here
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, Excel jeanie, RoryA addin (Win & Mac) or Borders-Copy-Paste

  3. #3
    Board Regular
    Join Date
    Jul 2009
    Posts
    595
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: XLSTART macro opens two ghost processes

    **** i did put a question.. Sorry.

    The issue is that this code opens two ghost processes with no active sheet inside them. Wondering if it can be fixed. Thanks

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com