modify VBA code to include dynamic columns instead of static

gms4b 

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.

gms4b 

I suspect that with the newest releases of Office 365 most of the things described here can be done easily with worksheet formulas.  The points to observe are that all raw data should be entered into Excel Tables so that the field to be searched for the value "Unknown" can be identified by name rather than a static numeric index.  Given that tables adjust dynamically to the data, no counts are required to determine the length of the column.

The key function is FILTER which will return all the records that match the criterion

= FILTER( Table1, Table1[field]="Unknown"} )

The advantage of names is as great when it comes to VBA.  A named field can be block-read into a variant array and then the looping is in memory rather than requiring continual exchanges between the VBA environment and the worksheet.  If the column changes, the Name moves with it, so the code is more robust.

gms4b

Hi,

You can do this by a simple algorithm:

• Search for the header of (Sample Type) in the worksheet using the Cell.Find method in VBA
• Store the column number of the Sample Type header in a variable called: headerColumn
• Use this variable in place of the number 4 in the IF statement's logical test

So the code looks like this:

Sub test()
    Application.ScreenUpdating = False
    
    Dim a As Long
    a = Worksheets("Raw Data").Cells(Rows.Count, 1).End(xlUp).Row
    
    Dim headerColumn As Long
    headerColumn = Cells.Find(What:="Sample Type", After:=Range("A1"), LookIn:=xlFormulas, _
                   LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                   MatchCase:=False, SearchFormat:=False).Column
                   
    For i = 2 To a
        If Worksheets("Raw Data").Cells(i, headerColumn).Value = "Unknown" Then
            Worksheets("Raw Data").Rows(i).Copy
            Worksheets("Undiluted").Activate
            Dim b As Long
            b = Worksheets("Undiluted").Cells(Rows.Count, 1).End(xlUp).Row
            Worksheets("Undiluted").Cells(b + 1, 1).Select
            ActiveSheet.Paste
            Worksheets("Raw Data").Activate
        End If
    Next
    
    Application.CutCopyMode = False
    ThisWorkbook.Worksheets("Raw Data").Cells(1, 1).Select
    Application.ScreenUpdating = True
End Sub

Hope that helps