Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Jun 2010
    Location
    Quincy, MA
    Posts
    62
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Macro Help Please

    Hi,

    I have a macro today that runs about 25 different instructions. I'd like to add to that so that I can Filter on Column A for a particular value, temporarily pause the macro [to delete what was filtered], then resume the macro. I can successfully do this in 2 macros without the pause, and I can successfully pause the macro; I unfortunately, cannot pause the macro AND delete the filtered data. The pause essentially locks out the user from doing anything for whatever period of time specified. Any insight into how I can keep this to one macro instead of two is appreciated. Regards, Pete

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,168
    Thanks
    47
    Thanked 978 Times in 908 Posts
    Write one macro that does both jobs, filter and delete. You can add a question to confirm deletion.

    cheers, Paul

  3. #3
    Star Lounger
    Join Date
    Jun 2010
    Location
    Quincy, MA
    Posts
    62
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Paul T View Post
    Write one macro that does both jobs, filter and delete. You can add a question to confirm deletion.

    cheers, Paul
    Thanks Paul, Pete

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    I am assuming you are looking at this approach because you want to avoid tearing down a complex procedure into parts

    While Paul is correct, try this approach. Create a global variable of the boolean type and a global variable as Long. As your code runs, branch = false, filters your data, changes branch to True then exits the sub. You then delete some filtered lines which triggers the Worksheet_Change event which looks to see if the number of lines has decreased. If the number of lines is less, it re-runs your same macro BUT because branch = True, it starts at the second part of your macro. The second part resets branch to false and continues with the rest of your code.


    Code:
    Public branch As Boolean, PrevLastRow As Long
    Public Sub MyCode()
    '---------------------------------------
    'BRANCH 1
    If branch = False Then
        code to filter records
        branch = True
        Exit Sub
    End If
    '---------------------------------------
    'BRANCH 2
    branch = False
    continue with code
    End Sub
    In the ThisWorkbook Module:
    Code:
    Private Sub Workbook_Open()
    PrevLastRow = Worksheet("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    branch = False
    End Sub
    In a standard module:
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
        If LastRow < PrevLastRow Then MyCode 'ROWS HAVE BEEN DELETED
        PrevLastRow = LastRow
    End Sub
    HTH,
    Maud
    Last edited by Maudibe; 2015-08-06 at 14:27.

  5. #5
    Star Lounger
    Join Date
    Jun 2010
    Location
    Quincy, MA
    Posts
    62
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Maud, this is excellent, thank you!

  6. #6
    New Lounger
    Join Date
    Aug 2015
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Need Marco to Read file Names from a folder and create text file

    Hi All

    Can you any of you help me in creating marco to read all filenames from a folder and create text file(CSV).
    My text file format should be

    File Name path,FileName,File extension

    thanks

  7. #7
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,168
    Thanks
    47
    Thanked 978 Times in 908 Posts
    Please don't double post. Your original thread has been answered.
    http://windowssecrets.com/forums/sho...iter-text-file

    cheers, Paul

Posting Permissions

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