+ Reply to Thread
Results 1 to 2 of 2

Stop running till the columns are deleted!

Hybrid View

  1. #1
    Registered User
    Join Date
    06-29-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    2

    Unhappy Stop running till the columns are deleted!

    I have an excel spreadsheet with a lot of empty columns. I have a main macro that has some lines of codes, then calls a macro that deletes empty columns, then runs more macros. The problem is that the columns do not finish deleting before other macros begins to run. I have tried using Application.Wait, but that causes the columns to stop deleting. If I run the macros one by one, each one works perfectly. When I Call them in the main macro, bad things happen. Very bad. Here is the main macro.

    Sub SG_RPAGSTDMMacro()
    
        Rows("1:1").Select
        Rows("1:55").Select
        Selection.Delete shift:=xlUp
        Cells.Select
        With Selection
            .VerticalAlignment = xlTop
            .Orientation = 0
            .AddIndent = False
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With
        Range("A1").Select
        Cells.Find(What:="*** i", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).Activate
        Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
        Selection.EntireRow.Delete
        Cells.Select
        Range("A1").Activate
        Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal
        Range("B1").Select
        Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _
            xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
        Cells.Select
        Call DelEmptyCols
        Call DoZ1CheckinX
        Call DeleteRowsifBelow0
        Call AutofitAll
    End Sub
    Here is the macro for DelEmptyCols

    Sub DelEmptyCols()
    ' Deletes all empty columns on the active worksheet
    Dim iCol As Integer
    With ActiveSheet.UsedRange
    For iCol = .Column + .Columns.Count - 1 To 1 Step -1
    If IsEmpty(Cells(65536, iCol)) And IsEmpty(Cells(1, iCol)) Then
    If Cells(65536, iCol).End(xlUp).Row = 1 Then Columns(iCol).Delete
    End If
    Next iCol
    End With
    End Sub

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    16,047

    Re: Stop running till the columns are deleted!

    Try this:

    Where in B columns are blank cells of rows that need to be deleted...
    Reverse Columns("B:B") to Rows(2:2) if you want opposite

    Columns("B:B").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    Never use Merged Cells in Excel

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1