+ Reply to Thread
Results 1 to 8 of 8

VBA to Extract Data using advanced Filter

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2024
    Posts
    2,888

    VBA to Extract Data using advanced Filter

    I have a macro to extract data from sheet "current Data" to sheet "New Account numbers Added"


     Sub Extract_Newaccount_Numbers()
    
     With Sheets("New Account numbers Added")
    Dim lr As Long
    
    
        lr = .Range("A" & .Rows.Count).End(xlUp).Row
      .Range("A1:F" & lr).ClearContents
      .Range("A1:F1").Interior.ColorIndex = xlNone
      End With
          With Sheets("New Account numbers Added")
       Sheets("Current Data").Columns("A:F").AdvancedFilter Action:=xlFilterCopy, _
            CriteriaRange:=Range("Criteria"), CopyToRange:=Range("A1"), Unique:=False
            .Range("A:F").EntireColumn.AutoFit
            End With
            
             
    End Sub
    the above code runs perfectly and extract the data


    However, If the range F2 to the last row in Col F is blank, then I would like the Macro to exit


    I have tried to write code to get this to work, but get a compile error: "Else without if

    See additional code below to exit sub if F2 to last row in col f is blank

     Sub Extract_Newaccount_Numbers()
    
     With Sheets("New Account numbers Added")
    Dim lr As Long
    
      
        lr = .Range("A" & .Rows.Count).End(xlUp).Row
      .Range("A1:F" & lr).ClearContents
      .Range("A1:F1").Interior.ColorIndex = xlNone
      End With
     With Sheets("Current Data")
     Dim LR1 As Long
      LR1 = .Range("A" & .Rows.Count).End(xlUp).Row
       If Range("f2:F" & LR1).Value = "" Then Exit Sub
        
       Else
       End If
        End With
         With Sheets("New Account numbers Added")
       Sheets("current data").Columns("A:F").AdvancedFilter Action:=xlFilterCopy, _
            CriteriaRange:=Range("Criteria"), CopyToRange:=Range("A1"), Unique:=False
            .Range("A:F").EntireColumn.AutoFit
            End With
            
             
    End Sub

    your assistance in resolving this is most appreciated

  2. #2
    Forum Guru Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2019 | 2021
    Posts
    15,032

    Re: VBA to Extract Data using advanced Filter

    By now you should no that a sample upload speaks a thousand words...
    Good Luck...
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the [★ Add Reputation] to left of post window...
    Also....Add a comment if you like!!!!
    And remember...Mark Thread as Solved...
    Excel Forum Rocks!!!

  3. #3
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    1,028

    Re: VBA to Extract Data using advanced Filter

    Try replacing these 3 lines:
     If Range("f2:F" & LR1).Value = "" Then Exit Sub
        
       Else
       End If
    with this 1 line:
    If WorksheetFunction.CountA(Range("f2:F" & LR1)) = 0 Then Exit Sub

  4. #4
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2024
    Posts
    2,888

    Re: VBA to Extract Data using advanced Filter

    thanks Akuini for your code

    If The range if F2 to the last row in Col F is blank, it works fine, however if there is data in Col f, then nothing is extracted

    I attached sample data


    In the sample data if Col F from row 2 is blank, Macro to be exited, otherwise extract data based in criteria on sheet "New Account numbers Added"
    Last edited by Howardc1001; 07-26-2019 at 07:09 AM.

  5. #5
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2024
    Posts
    2,888

    Re: VBA to Extract Data using advanced Filter

    You are 100% correct

    attached please find sample data
    Last edited by Howardc1001; 07-26-2019 at 07:07 AM.

  6. #6
    Forum Guru Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2019 | 2021
    Posts
    15,032

    Re: VBA to Extract Data using advanced Filter

    With your With statements...Ensure to insert a point to depict the range pertaining to that sheet
    Option Explicit
    
    Sub Extract_Newaccount_Numbers()
    Dim lr As Long
    With Sheets("New Account numbers Added")
        lr =.Range("B" & .Rows.Count).End(xlUp).Row
        .Range("A1:F" & lr).ClearContents
        .Range("A1:F1").Interior.ColorIndex = xlNone
    End With
    With Sheets("Current Data")
        lr = .Range("A" & .Rows.Count).End(xlUp).Row
        If WorksheetFunction.CountA(.Range("F2:F" & lr)) = 0 Then Exit Sub
    End With
    With Sheets("New Account numbers Added")
        Sheets("current data").Columns("A:F").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Criteria"), CopyToRange:=.Range("A1"), Unique:=False
        .Range("A:F").EntireColumn.AutoFit
    End With
    End Sub
    Last edited by Sintek; 07-26-2019 at 08:18 AM.

  7. #7
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2024
    Posts
    2,888

    Re: VBA to Extract Data using advanced Filter

    Thanks for the help Sintek. Code works perfectly

  8. #8
    Forum Guru Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2019 | 2021
    Posts
    15,032

    Re: VBA to Extract Data using advanced Filter

    Thanks for rep+...please mark thread as solved...

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Advanced Filter - extract range missing or invalid field name
    By sorensjp in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-19-2019, 04:07 AM
  2. Advanced Filter Extract Range Error
    By rob_h in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-30-2019, 06:50 PM
  3. Extract data using Advanced Filter and VBA
    By vadivel77 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-08-2015, 12:22 PM
  4. [SOLVED] Advanced Filter - extract data to new sheet
    By schnautza in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 01-28-2014, 02:43 PM
  5. Replies: 4
    Last Post: 08-02-2010, 07:16 AM
  6. [SOLVED] missing or ill egal extract range advanced filter
    By tjtjjtjt in forum Excel General
    Replies: 3
    Last Post: 07-10-2005, 06:05 PM

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