VBA, Advanced Filter

  • Re: VBA, Advanced Filter


    I have noticed a slight flaw in the logic of the code, that may result in problems if there was to be no Node column in a worksheet. I have made some changes to correct that, and to fill down the sheet name, although these are untested:

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

  • Re: VBA, Advanced Filter


    Obviously sabotaged by the Joker or Penguin! Or more likely it's a problem as I couldn't test it before I posted the code. I will have a look at it, this time in conjunction with the workbook.


    However, the Node and Scenario columns are independently filtered and displayed in the Unique Data sheet. Arbitrarily filling down the last item in the shorter of the two lists within a worksheet may be creating an artificial correlation between two items that may not exist as a combination within the original worksheet. Do you really want those gaps filled?

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

  • Re: VBA, Advanced Filter


    This version fills down the file and sheet names, but not the Node and Scenario values, as you are supposed to be listing unique entries. It also corrects the listing of the file and sheet names in the wrong columns.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

  • Re: VBA, Advanced Filter


    Your right, it works, might as well use your suggestion to not fill down the node and scen name. Aslong as it pivots and can be filtered, this would be a better suggestion by you actually.

  • Re: VBA, Advanced Filter


    Batman, How would I fill down in the other columns aswell? Just curious. It helps filtering the unique data sheet.
    Is it a simple .filldown? I'm eager to learn this.

  • Re: VBA, Advanced Filter


    Hi,


    Sorry it took me a while to spot the symbol in the clouds! This is the code that does the filling down:

    Code
    lngNextRow = WorksheetFunction.Max(wksSummary.Cells(wksSummary.Rows.Count, 3).End(xlUp).Row, wksSummary.Cells(wksSummary.Rows.Count, 4).End(xlUp).Row) + 1
        If (lngNextRow - lngStartRow) > 1 Then
            z.Resize(lngNextRow - lngStartRow, 2).FillDown
        End If


    The first line of code performs two Ctrl & Up Arrow functions, on columns C & D, then uses the worksheet function MAX to find the greater of the two. It is then that number (+1) that is used to determine the next row for the next sheet.


    The second line of code (If...) checks the new next line against a previously saved first line. If the difference is not greater than 1, then there is no need to fill down.


    The third line of code actually fills down. The variable 'z' has previously been defined as a single cell in column A of the row in which you start pasting the values. The 'Resize' property allows you to take that start point and make it refer to a range of cells of a different number of rows and columns, but starting with the original cell at the top left of the new range (first resize number is the new number of rows, second number is the number of columns). In this case the number of rows is calculated as the next row minus the start row. The number of columns is hard coded as 2. The first row of the new range needs to contain what you are going to fill down.


    If you wanted to fill down, say, columns G & H, the code would change to

    Code
    z.Offset(0, 6).Resize(lngNextRow - lngStartRow, 2).FillDown


    This does the same thing, except that it first uses Offset to move the start point. The start point moves 0 rows and 6 columns to the right, i.e. from column A to column G of the same row.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

  • Re: VBA, Advanced Filter



    Thanks Batman! For some reason it actually doesn't fill down? It moreso fills up. and makes 1 value of everything.

  • Re: VBA, Advanced Filter


    When you say 'the other columns', do you mean that you now want to duplicate the unique Node and Scenario names in columns C & D? Or is there other data that you will be copying to different columns, and you want to fill that down as well? I must admit, I had assumed the latter.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

  • Re: VBA, Advanced Filter


    Quote from Batman;755146

    When you say 'the other columns', do you mean that you now want to duplicate the unique Node and Scenario names in columns C & D? Or is there other data that you will be copying to different columns, and you want to fill that down as well? I must admit, I had assumed the latter.


    Yes, Node and Scen names, Sorry i wasnt specific. I meant your first statement.

  • Re: VBA, Advanced Filter


    Hi,


    This should work.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

  • Re: VBA, Advanced Filter


    Hi Batman!! :),


    If you have time, Would you be alter that existing code we talked about(post 54), I altered it abit to skip unreadable files.




    To include this code which does a row percentage calculation of a workbook(rows filled/# of rows) and populates in the last row of each sheet of a workbook. I want to take these results and put it into the 'unique data' sheet previously created or in a new sheet. I just need help implementing the beneath code into the previously one you did.


  • Re: VBA, Advanced Filter


    Hi,


    What part of the process are you having problems with?


    I assume that, as you are introducing new data into your worksheets, this will change the layout of your data and also where you want to place the results, neither of which I can define for you.


    As you have all the logic you need, I would suggest that you copy the code within the worksheet loop to where you think it best fits, and then put a breakpoint in the program and scroll through the program one line at a time (F8) and check whether it is doing everything in the correct order, and based on the right data. You can then just keep amending the column references, or other variables, and re-run the process until it does what you want.


    By the way, I would suggest that you replace the 'ActiveWorkbook' object with the 'wb' variable. It is much more reliable, and there seems to be little point in assigning the 'wb' variable only to then ignore it in favour of the less reliable 'ActiveWorkbook..

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

  • Re: VBA, Advanced Filter



    Problem incorporating the 2nd code into the first and formatting that unique data sheet in a nice way or another sheet in the format of file name sheet name column a and b percentages. Either or. It's more so the actual format of the results of the 2nd code .


    Because you said its hard to define because its new results, would you be able to help with a new sheet then similar to that of the unique data one?

  • Re: VBA, Advanced Filter


    Actually I tried doing its on its own since you said you couldnt define and might get hard, so I did it without the previous unique data sheet. But it does still need formatting results into a new sheet in a similar format of post 54., check out what i got:


  • Re: VBA, Advanced Filter


    It's not necessarily hard, just difficult without being able to see the format of the data that you are reading, what additional data you want to create, and where, and where you want all the results to go.


    Provided that you understand what all your code is doing, it should only be a question of you deciding which bits to action, and in which order, to get to the desired result. But only you should decide what you want the result to be, not me.


    I might be able to have a look at this, but I would need to see the format of the various files your are reading, what additional data you want to create and where you want to put it, and exactly what the format of the output needs to be. Also, I have very little time over the next week or two and can't guarantee to be able to look at it for a while - it is only by chance that I logged on to the forum and saw your latest post.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!