#4: How to loop through all rows and copy filtered data to a new sheet?

Feb 10, 2021

Clinical Research Data 101

Tools/Languages: Excel VBA Macro

This tutorial shows you how to automatically loop through all rows, find the IDs with missing data access group (DAG) names, copy/paste the filtered data to a new sheet and save the new sheet in one click.

It's super helpful if you have more than 100 participants from more than 1 site.
Or when you need to generate a list of participants with missing site names or numbers.

First of all, here's the link to learn how to turn on the "Developer Mode" in your Excel.

What happened?

In clinical research, we often need to track participants' status. Usually we would have a spreadsheet of participants IDs with previously assigned site numbers. For example, UCLA is site 01 and UC Santa Cruz is site 02, etc. In the electronic data capture system (REDCap), the site numbers should be used as the best indicator for sites, since it's easy to mispell the site names and there're multiple ways to write the same site names. This tutorial is to simulate the situation when you export the report data to Excel spreadsheet as raw data.

In the online EDC, hundreds of records are entered by site coordinators from different institutions, you'd like to quickly generate a list of participants who have not been assigned to a site (a typical error on the site), knowing that the missing data is coded as either 99 or 0.
Your goal is to first find all IDs with missing DAG and then copy the whole row to a new sheet. In this case, you can create a loop to accomplish that.

What should I do now?

• Open the Excel spreadsheet and turn on the "Developer" mode as described at the beginning.
• Click "Visual Basic" on the top ribbon (first one on the left).
• Insert a new "Module" and copy/paste the codes below.

  
  Sub FindMissingDAG()
  
  Dim site As Double
  
  Dim dag As String
  
  Dim wb As Workbook
  
  Dim today As String
  
  Dim counter As Integer
  
  Dim intloc As Integer
  
  lastrow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
  
  counter = 2
  
  today = DateValue(Now)
  
  todaydate = Replace(today, "/", "_")
  
  MsgBox todaydate
  
  'Adding a new sheet named "Missing_DAG"
  
  Sheets.Add(After:=Sheets("Sheet1")).Name = "Missing_DAG"
  		
  'Copying the heading variables to the new sheet
  
  Worksheets("Missing_DAG").Range("A1:D1").Value = Sheets("Sheet1").Range("A1:D1").Value
  
  'Looping through all rows to find all IDs with missing DAGs (site number: 99 or 0)
  
  For myrow = 2 To lastrow
  
  intloc = Sheets("Sheet1").Range("D" & myrow).Value
  
  If intloc = 99 Or intloc = 0 Then
  
  
  Worksheets("Missing_DAG").Range("A" & counter & ":D" & counter).Value = Worksheets("Sheet1").Range("A" & myrow & ":D" & myrow).Value
  
  
  counter = counter + 1
  		
  Else
  
  End If
  
  Next myrow
  
  'save the file in mac
  
  Worksheets("Missing_DAG").SaveAs "/Users/aq/Desktop/Tutorials/StudyName_Missing_DAG_" & todaydate & ".csv"
  
  End Sub
  							
  						

• Go back to the spreadsheet and click the "Macros" icon on the top ribbon. Then assign the macro to "dag" and click "OK".

Please leave me a message if it doesn't work for you.