VBA to remove Unneeded rows of data.

AllBonesJones · Oct 17, 2022

Hi, I'm fairly new to VBA and I'm at a loss as to where to even start for this. I'm looking to use VBA to remove large amounts of unneeded data. I'm using machine logs to do time calculations spent on the various machine processes. The logs contain data for every second of each sequence in the process. Each process can contain 1 - 50 sequences and between 300 and 3000 lines of data per each sequence. The sequence name and position of these data points vary greatly depending on design and machine). I only need 3 data points per sequence, the first point, last point and the first "0" value that occurs 2-5 minutes into each sequence. Column A contains the time stamp, B is the sequence name (which can repeat multiple times throughout a design), and C contains the measured data throughout the sequence (including the "0" value needed). Based on the image below, I'm looking to delete rows 3, 5, 6, 9, 11, 12, ect.... Any help in this matter would be greatly appreciated.

DanteAmor · Oct 17, 2022

Hi and welcome to Mr Excel.
So you can have up to 15 thousand records. Deleting is always going to be a very slow process.

So the following macro does not delete records, what it does is select the records you need. The result puts it in columns E through G.
That way you keep the original data and the new data.
But if you still don't want the originals, then just change in the macro, in the last line "E2" to "A2" and that's it.
Range("E2").Resize(UBound(b, 1), 3).Value = b

Copy all the code into a new module:

VBA Code:

Option Explicit

Dim y As Long

Sub remove_Unneeded_rows()
  Dim dic As Object
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long
  Dim ant As String
  Dim first0 As Boolean
 
  a = Range("A2:C" & Range("C" & Rows.Count).End(3).Row + 1)
  ReDim b(1 To UBound(a, 1), 1 To 3)
  Set dic = CreateObject("Scripting.Dictionary")
  Range("E:G").ClearContents
  ant = a(1, 2)
  y = 0
 
  For i = 1 To UBound(a, 1)
    If a(i, 2) = ant Then
   
      If Not dic.exists(a(i, 2)) Then
        Call adding(dic, a, b, i)     'first point
        first0 = False
      Else
        If a(i, 3) = 0 And first0 = False Then
          first0 = True
          Call adding(dic, a, b, i)   'first "0"
        End If
      End If
     
    Else
   
      'For the last point the parameter that is sent is 'j'
        j = i - 1
        If dic(a(j, 2)) <> j Then
          Call adding(dic, a, b, j)   'Last point
        End If
      'Last point
     
      dic.RemoveAll
      If a(i, 2) <> "" Then
        Call adding(dic, a, b, i)     'first poin
        first0 = False
      End If
     
    End If
    ant = a(i, 2)
    Range("E2").Resize(UBound(b, 1), 3).Value = b
  Next
 
  Range("E2").Resize(UBound(b, 1), 3).Value = b
End Sub

Sub adding(dic, a, b, i)
'Adding data to the output array
  y = y + 1
  dic(a(i, 2)) = i
  b(y, 1) = a(i, 1)
  b(y, 2) = a(i, 2)
  b(y, 3) = a(i, 3)
End Sub

-------
HOW TO INSTALL MACROs
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (remove_Unneeded_rows) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

-------
NOTE XL2BB:
For the future, it would help greatly if you could give us the sample data in a form that we can copy to test with, rather that a picture.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

-----
Example:

Dante Amor

A

B

C

D

E

F

G

1

Time

SEQ

XTC

Time

SEQ

XTC

2