Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Feb 2015
    Posts
    128
    Thanks
    19
    Thanked 1 Time in 1 Post

    Post Want to change the range from table to dynamic.

    Hi Experts

    I need help to change the range in the VBA from table to dynamic. The reason for it is that I need to share the Macro workbook,but since it contains table, excel won't allow me to share it. The column range of the data in the Rawdata sheet is is A:Q


    Code:
    Sheets("RawData").Range("Table1[#All]").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
            Sheets("RawData").Range("S1:S2"), CopyToRange:=Sheets("Filter").Range("B10"), Unique:=True
        Columns.AutoFit
    Code:
    Sub FilterData()
        Application.ScreenUpdating = False
        Sheets("Filter").Select
        Range("B10").Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Clear
       
        Sheets("RawData").Range("Table1[#All]").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
            Sheets("RawData").Range("S1:S2"), CopyToRange:=Sheets("Filter").Range("B10"), Unique:=True
        Columns.AutoFit
        Range("B11").Select
        Application.ScreenUpdating = False
    End Sub

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Jaggi,

    Here's a little VBA Code and a Form that you can import into your VBA project that will allow you to create Dynamic Range Names easily.
    ufDynRangeName.zip
    Unzip the attached file and import the .frm file into your VBA project via the VBE File -> Import command.
    Note if you want to include entire columns into your range just use the Max Rows in your version of Excel but if you want it to be portable don't use more rows than are available in Excel 2003! Then just use the range name created in your formulas and VBA code.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi

    You cannot use AdvancedFilter in a shared workbook.

    zeddy

  4. #4
    2 Star Lounger
    Join Date
    Feb 2015
    Posts
    128
    Thanks
    19
    Thanked 1 Time in 1 Post
    Hi Zeddy and RetiredGeek

    I think it is better to stay away from the share mode, because it is a Advance filter.

    Regards,
    JD

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Jaggi

    I agree. Avoid using shared workbooks if you can.

    I have attached an example file which used named ranges with the AdvancedFilter VBA method.
    (But this won't work if the workbook is shared!)
    This uses sample data, but you can adapt the method to your needs.
    If you need any further info, just ask.

    zeddy
    Attached Files Attached Files

  6. #6
    2 Star Lounger
    Join Date
    Feb 2015
    Posts
    128
    Thanks
    19
    Thanked 1 Time in 1 Post

    Post

    Hi Zeddy
    I am able to share your attached macro. To make it share I disabled the developer option and then try to save it. It worked! Please try it your end and you will be able to share it.
    I have also attached the macro which I am working with. There is a filter condition in the “Filter” sheet of the macro. It helps me to filter specific data from the RawData sheet into the Filter sheet. The issue is with the table in the RawData sheet of my macro. Could you please help me to remove the table for the RawData sheet of the attached macro?
    Regards,
    JD
    Attached Files Attached Files

  7. #7
    2 Star Lounger
    Join Date
    Feb 2015
    Posts
    128
    Thanks
    19
    Thanked 1 Time in 1 Post
    Hi Zeddy

    Thanks for the macro. I tweak it in my macro and able to replace the table with the code.

    Looking forwar to make it shared and have to set some password protection on column A and D and have to give access to rest of the columns to the users. Let see how I manage to do that.

    Regards,
    JD

Tags for this Thread

Posting Permissions

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