Forum Discussion
modify VBA code to include dynamic columns instead of static
I am still somewhat puzzled as to the reason you turn so readily to VBA for the analysis. Excel has a huge amount of built-in functionality to support filtering, both as manual operations and automatic, using formulas,
I ran the code in "Example with VBA4.xlsm" this morning and it took 1min20sec to produce 630 rows for 'undiluted' and a further 30 on the 'diluted' sheet. I am sure there is plenty of room for speed optimisation of the code but I went on to compare that time with that of a formula-based approach.
I happened to be using a machine with Office 365 loaded so, having converted the source data to a table, I used a modern dynamic array formula to extract the required records. The formulas
= FILTER( RawData,
((RawData[Sample Type]="Unknown") +
(RawData[Sample Type]="Quality Control")) *
(RawData[Dilution Factor]=1) )
and
= FILTER( RawData,
((RawData[Sample Type]="Unknown") +
(RawData[Sample Type]="Quality Control")) *
(RawData[Dilution Factor]>1) )
each took 0.1sec to run and gave identical output.
p.s. Other options would include running the Advanced Filter from code (any version of Excel) or to use Power Query (Office 2010 with add-in, native 2016 and on).
I have now attached a copy of the file with your early VBA, with Power Query and with Dynamic Array formulas. The last only works with the most up-to-date versions of Office365.
Thanks Peter. I'll start to look into this. In the end, the whole process is:
- dividing Unknown and Quality Control samples between diluted and undiluted groups (Then get rid of columns that I don't need)
- Adding some columns to each of these sets to do some math do determine if the samples are in a range
- Then a VLOOKUP to compare samples in the diluted and undiluted groups and choose the proper one for final data.
This has been a very manual process not just for me, but my teammates. I wanted to make a way for people to simply add in all the raw data into the first tab of a spreadsheet, press a button, and get final data. So, that's why I thought VBA would be a good way to automate the whole process.
If i can find a way to do the filtering (as you suggest) and replace the first part of the code, then that sounds like a good idea. As you mentioned.....it can be rather slow. And, its possible that 1000's of rows of data could be in a set.
Thanks,
Greg
Do you and any colleagues that need to manipulate the results have Office 365 and can you set it to monthly updates? The array methods I have used have only come out in the latest releases of Excel and, though it would be possible to transfer the data using INDEX it would lack elegance.
Removing columns could be achieved by being more selective as to the columns brought through in the first place, even down to the level of bring key columns across individually within the downstream calculation.
Yes, we all have Office 365 ProPlus...with automatic updates.
I did some work last week to include filtering in the code....and it worked well! I did it with a latter part of the script though, not the first part that you've looked at. I'm not sure its what you envisioned...but it worked (just using the simple "sort" feature). Basically, not being a programmer, I simply recorded a macro with what and where I needed to filter and copied and pasted the rows between sheets. I then took the code from the macro and inserted into my main code. I needed to modify it a bit to always go down the the last row (since they will always be changing), but that worked well (even though you select a whole column while recording a macro it inserts a number for the last row). But, yeah, it worked.
Since it worked well toward the end of the code I'll be going back and doing that eventually for the first part of the code....because, as you mentioned, that first part of the code is quite time consuming. And my data sets will only get larger.
Unfortunately, the raw data from our instruments exports columns into different locations each time. Haytham Amairah wrote a nice bit of code (below) for me to move only columns that are included in the array to a new sheet. I'll likely change the code to run this first, then do sorting when the columns are static.
Greg-----------------------
Sub MoveToUndilutedPLUS()
Application.ScreenUpdating = False
On Error Resume Next
Dim i As Integer
i = 0
Sheets("Undiluted").Activate
Dim columnNamesArray() As Variant
columnNamesArray = Array("Sample Type", "Sample Name", "Acquisition Date", "File Name", "Dilution Factor", "Analyte Peak Name", "Calculated Concentration (", "Analyte Concentration", "Accuracy")
Dim columnName As Variant
For Each columnName In columnNamesArray
Sheets("Undiluted").Activate
Dim columnNumber As Integer
columnNumber = Cells.Find(What:=columnName, After:=Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Column
Cells(1, columnNumber).EntireColumn.Copy
Sheets("UndilutedPLUS").Activate
Sheets("UndilutedPLUS").Range("B1").Select
ActiveCell.Offset(0, i).Select
ActiveSheet.Paste
i = i + 1
Next columnNameRather than searching for the column headers as text, I would recommend converting the raw data to a Table and selecting the columns you require by name. The formula I used to consolidate the columns into a single array that I could filter is a bit of a pig but it appears to work. I implemented it as a Named Formula 'SelectedData' that could then be filtered as before:
= CHOOSE( {1,2,3,4,5,6,7,8,9},
RawData[Sample Type],
RawData[Sample Name],
RawData[Acquisition Date],
RawData[File Name],
RawData[Dilution Factor],
RawData[Analyte Peak Name],
RawData[Calculated Concentration (ng/mL)],
RawData[Analyte Concentration (ng/mL)],
RawData[Accuracy (%)] )
I guess both the VBA and the formulae must present something of a problem for you to follow. I tend to lose or simply bewilder spreadsheet users when I describe the practice of addressing data by location (A1 or, in VBA, Cells(1,1)) as an abomination that has no place in serious model building. For me, the name is meaningful whilst the location is mere chance and will change as data is rearranged within the workbook. In your case, it simply doesn't matter in what order the columns are placed as long as they are named consistently.
Please note that most users would vehemently disagree with the underlined statement, they would see the A1 notation as integral to the concept of a spreadsheet.