How to filter & sort columns to get data in a continuous range

Hi Wizards,

I've a very basic knowledge about sorting and filtering with VBA. Which is the easiest way to sort / filter one or more column(s) so as to always get a continuous range of data?

I've a big table of data extracted from a database into a worksheet with one particular column containing user ids and their names (in combination) and few other columns containing different formats of data.

I need to spruce up this worksheet and remove unwanted data before i use another function to randomly select rows of data. My requirement is to get continuous range of data when filters are applied to either single or muliple columns. This filtered range of data will be used by the random selection function.

Sarang

Here's a sample workbook. There a column called LAST_UPDATE_NAME where there are a list of names.

My ultimate requirement is to sort / filter data and get it in a continuous range. I may need to apply filters to drill down to a final set of data. The final data that i need should be in a continuous range. Filtering can be done upto 3 or 4 levels.

Initially, need to do sort the names column and then filter by each name and get a continuous range of data, but then while drilling down based on various other columns such as KYL_ML_SCORE, ORG_UNIT_NAME, NOTES, RISK WEIGHTING, etc. i want to get the filtered output (data) in a continous range. At any given time, after filtering by the names column, i may drill down to the final set of data by applying filter to one or more of the above four columns.

How can i get the final data in a continuous range (after all filters are applied)? Can anybody help me develop a piece of code? Any ideas??


Sarang

Hi,

The background for this dynamic drill-down exercise is to arrive at a set of rows of data based on 4 other columns mentioned above whose range will be used by another function (which needs a continuous range).

How can this drill-down be done effectively??

Sarang

Hi,

I tried modifying a code module for my requirement. The code (which i built for another requirement) below filters and transfers the filtered data into a new worksheet. Filters can be applied one or more columns.

Please Login or Register  to view this content.

Need some help to modify the above code to suit my requirement. Instead of transferring the filtered data to a new sheet, i need to make the rows in the filtered range continuous (rows to be rearranged by sorting or some other way). How can that be done? Can someone help??


Sarang

Attached herewith is the workbook with the code that i tried out (given above).

The data needs to be sorted automatically (as in the output sheet) with the filter still active as i want to pick random rows of data for which the filtered output needs to be in continuous range passed to the function developed by Pattrick Mathews @ VBAX (below). The filtering and shuffling of rows will be done for all the user names in the worksheet (for whom samples have to be chosen) and will be done with a for loop.

Please Login or Register  to view this content.

The function takes a Range variable which should to be continuous to choose a set of rows randomly. Can someone help me how to go about selecting a continous range of data from the Extract data sheet?


Sarang

Is there any other way to get a continuous range of data for random selection of rows from a worksheet with thousands of data rows?

there are 2 cases in which a filtered database will result in a continuous range:

- all the records will be selected (but then filtering is redundant)
- the records have been sorted on the filter criterion before filtering

if you use more than 1 filtercriterion the result will be a non-continuous range (which is of course the essence of 'filtering').

If you want the filtered result being represented as a continuous range you have to copy the filterresults to another worksheet (preferably using advancedfilter).

The primary column (the first column which contains the user names on which filtered is applied) is sorted first before anything is done. The Filter_N_Transfer module code above does transfer rows to a new worksheet (filters can be applied to any number of columns and renames that sheet as "Not Set", however instead of creating new sheet, can the records be shuffled / sorted within the source sheet itself??

Let me forget about the code i've built here for the time being.. Can someone explain about how rows can be reshuffled easily by sorting or filtering??

No response bump !!