Removing rows that contain a certain value

[mlarson]

What I would like to do is to create a macro that automatically removes rows (from the spreadsheet below) that have any of the following:
(1) a value <0.30 in column G, or
(2) a value >25.00 in column G, or
(3) a value >10 in column I

Here is my spreadsheet and the desired output. As you can see rows 2, 8, 9, 10, 11 were removed due to the value in column G being either <0.30 or > than 25.00. Also, rows 15, 16, 17 were removed because the value in column I is > 10. Do you know of a macro that would be able to do this automatically? Thanks in advance for your help.

ORIGINAL SPREADSHEET
<table width="686" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64"> <col style="width: 57pt;" width="76"> <col style="width: 48pt;" width="64" span="6"> <col style="width: 26pt;" width="34"> <col style="width: 48pt;" width="64" span="2"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 48pt;" width="64" height="20">A</td> <td class="xl63" style="width: 57pt;" width="76">B</td> <td class="xl63" style="width: 48pt;" width="64">C</td> <td class="xl63" style="width: 48pt;" width="64">D</td> <td class="xl63" style="width: 48pt;" width="64">E</td> <td class="xl63" style="width: 48pt;" width="64">F</td> <td class="xl63" style="width: 48pt;" width="64">G</td> <td class="xl63" style="width: 48pt;" width="64">H</td> <td class="xl63" style="width: 26pt;" width="34">I</td> <td class="xl63" style="width: 48pt;" width="64">J</td> <td class="xl63" style="width: 48pt;" width="64">K</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">2</td> <td class="xl64">8/11/2010</td> <td class="xl65">14:33</td> <td class="xl66">0.19</td> <td class="xl66">0.19</td> <td class="xl66">0.19</td> <td class="xl66">0.19</td> <td class="xl63">L</td> <td class="xl63">2</td> <td class="xl63">XRZ</td> <td class="xl63">540</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">3</td> <td class="xl64">8/11/2010</td> <td class="xl65">14:34</td> <td class="xl66">0.35</td> <td class="xl66">0.35</td> <td class="xl66">0.35</td> <td class="xl66">0.35</td> <td class="xl63">R</td> <td class="xl63">2</td> <td class="xl63">THE</td> <td class="xl63">540</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">4</td> <td class="xl64">8/11/2010</td> <td class="xl65">14:35</td> <td class="xl66">1.00</td> <td class="xl66">1.00</td> <td class="xl66">1.00</td> <td class="xl66">1.00</td> <td class="xl63">Z</td> <td class="xl63">2</td> <td class="xl63">ODJ</td> <td class="xl63">540</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">5</td> <td class="xl64">8/11/2010</td> <td class="xl65">14:36</td> <td class="xl66">1.25</td> <td class="xl66">1.25</td> <td class="xl66">1.25</td> <td class="xl66">1.25</td> <td class="xl63">A</td> <td class="xl63">2</td> <td class="xl63">EIS</td> <td class="xl63">540</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">6</td> <td class="xl64">8/11/2010</td> <td class="xl65">14:37</td> <td class="xl66">0.57</td> <td class="xl66">0.57</td> <td class="xl66">0.57</td> <td class="xl66">0.57</td> <td class="xl63">B</td> <td class="xl63">2</td> <td class="xl63">EIA</td> <td class="xl63">540</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">7</td> <td class="xl64">8/11/2010</td> <td class="xl65">14:38</td> <td class="xl66">0.85</td> <td class="xl66">0.85</td> <td class="xl66">0.85</td> <td class="xl66">0.85</td> <td class="xl63">E</td> <td class="xl63">2</td> <td class="xl63">ISE</td> <td class="xl63">540</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">8</td> <td class="xl64">8/11/2010</td> <td class="xl65">14:39</td> <td class="xl66">0.26</td> <td class="xl66">0.26</td> <td class="xl66">0.26</td> <td class="xl66">0.26</td> <td class="xl63">U</td> <td class="xl63">2</td> <td class="xl63">VDE</td> <td class="xl63">540</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">9</td> <td class="xl64">8/11/2010</td> <td class="xl65">14:40</td> <td class="xl66">0.05</td> <td class="xl66">0.05</td> <td class="xl66">0.05</td> <td class="xl66">0.05</td> <td class="xl63">I</td> <td class="xl63">2</td> <td class="xl63">GOE</td> <td class="xl63">540</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">10</td> <td class="xl64">8/11/2010</td> <td class="xl65">14:41</td> <td class="xl66">0.15</td> <td class="xl66">0.15</td> <td class="xl66">0.15</td> <td class="xl66">0.15</td> <td class="xl63">Y</td> <td class="xl63">2</td> <td class="xl63">DJR</td> <td class="xl63">540</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">11</td> <td class="xl64">8/11/2010</td> <td class="xl65">14:42</td> <td class="xl66">0.14</td> <td class="xl66">0.14</td> <td class="xl66">0.14</td> <td class="xl66">0.14</td> <td class="xl63">R</td> <td class="xl63">2</td> <td class="xl63">DUA</td> <td class="xl63">540</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">12</td> <td class="xl64">8/11/2010</td> <td class="xl65">14:43</td> <td class="xl66">0.90</td> <td class="xl66">0.90</td> <td class="xl66">0.90</td> <td class="xl66">0.90</td> <td class="xl63">Z</td> <td class="xl63">2</td> <td class="xl63">LEZ</td> <td class="xl63">540</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">13</td> <td class="xl64">8/11/2010</td> <td class="xl65">14:44</td> <td class="xl66">1.50</td> <td class="xl66">1.50</td> <td class="xl66">1.50</td> <td class="xl66">1.50</td> <td class="xl63">B</td> <td class="xl63">2</td> <td class="xl63">WQR</td> <td class="xl63">540</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">14</td> <td class="xl64">8/11/2010</td> <td class="xl65">14:45</td> <td class="xl66">1.30</td> <td class="xl66">1.30</td> <td class="xl66">1.30</td> <td class="xl66">1.30</td> <td class="xl63">E</td> <td class="xl63">2</td> <td class="xl63">POR</td> <td class="xl63">540</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">15</td> <td class="xl64">8/11/2010</td> <td class="xl65">14:46</td> <td class="xl66">1.90</td> <td class="xl66">1.90</td> <td class="xl66">1.90</td> <td class="xl66">1.90</td> <td class="xl63">A</td> <td class="xl63">30</td> <td class="xl63">RTU</td> <td class="xl63">540</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">16</td> <td class="xl64">8/11/2010</td> <td class="xl65">14:47</td> <td class="xl66">2.50</td> <td class="xl66">2.50</td> <td class="xl66">2.50</td> <td class="xl66">2.50</td> <td class="xl63">L</td> <td class="xl63">30</td> <td class="xl63">JSS</td> <td class="xl63">540</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">17</td> <td class="xl64">8/11/2010</td> <td class="xl65">14:48</td> <td class="xl66">2.51</td> <td class="xl66">2.51</td> <td class="xl66">2.51</td> <td class="xl66">2.51</td> <td class="xl63">L</td> <td class="xl63">30</td> <td class="xl63">MAA</td> <td class="xl63">540</td> </tr> </tbody></table>
DESIRED OUTPUT
<table width="686" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64"> <col style="width: 57pt;" width="76"> <col style="width: 48pt;" width="64" span="6"> <col style="width: 26pt;" width="34"> <col style="width: 48pt;" width="64" span="2"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 48pt;" width="64" height="20">A</td> <td class="xl65" style="width: 57pt;" width="76">B</td> <td class="xl65" style="width: 48pt;" width="64">C</td> <td class="xl65" style="width: 48pt;" width="64">D</td> <td class="xl65" style="width: 48pt;" width="64">E</td> <td class="xl65" style="width: 48pt;" width="64">F</td> <td class="xl65" style="width: 48pt;" width="64">G</td> <td class="xl65" style="width: 48pt;" width="64">H</td> <td class="xl65" style="width: 26pt;" width="34">I</td> <td class="xl65" style="width: 48pt;" width="64">J</td> <td class="xl65" style="width: 48pt;" width="64">K</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">2</td> <td class="xl66">8/11/2010</td> <td class="xl67">14:34</td> <td class="xl68">0.35</td> <td class="xl68">0.35</td> <td class="xl68">0.35</td> <td class="xl68">0.35</td> <td class="xl65">R</td> <td class="xl65">2</td> <td class="xl65">THE</td> <td class="xl65">540</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">3</td> <td class="xl66">8/11/2010</td> <td class="xl67">14:35</td> <td class="xl68">1.00</td> <td class="xl68">1.00</td> <td class="xl68">1.00</td> <td class="xl68">1.00</td> <td class="xl65">Z</td> <td class="xl65">2</td> <td class="xl65">ODJ</td> <td class="xl65">540</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">4</td> <td class="xl66">8/11/2010</td> <td class="xl67">14:36</td> <td class="xl68">1.25</td> <td class="xl68">1.25</td> <td class="xl68">1.25</td> <td class="xl68">1.25</td> <td class="xl65">A</td> <td class="xl65">2</td> <td class="xl65">EIS</td> <td class="xl65">540</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">5</td> <td class="xl66">8/11/2010</td> <td class="xl67">14:37</td> <td class="xl68">0.57</td> <td class="xl68">0.57</td> <td class="xl68">0.57</td> <td class="xl68">0.57</td> <td class="xl65">B</td> <td class="xl65">2</td> <td class="xl65">EIA</td> <td class="xl65">540</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">6</td> <td class="xl66">8/11/2010</td> <td class="xl67">14:38</td> <td class="xl68">0.85</td> <td class="xl68">0.85</td> <td class="xl68">0.85</td> <td class="xl68">0.85</td> <td class="xl65">E</td> <td class="xl65">2</td> <td class="xl65">ISE</td> <td class="xl65">540</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">7</td> <td class="xl66">8/11/2010</td> <td class="xl67">14:43</td> <td class="xl68">0.90</td> <td class="xl68">0.90</td> <td class="xl68">0.90</td> <td class="xl68">0.90</td> <td class="xl65">Z</td> <td class="xl65">2</td> <td class="xl65">LEZ</td> <td class="xl65">540</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">8</td> <td class="xl66">8/11/2010</td> <td class="xl67">14:44</td> <td class="xl68">1.50</td> <td class="xl68">1.50</td> <td class="xl68">1.50</td> <td class="xl68">1.50</td> <td class="xl65">B</td> <td class="xl65">2</td> <td class="xl65">WQR</td> <td class="xl65">540</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">9</td> <td class="xl66">8/11/2010</td> <td class="xl67">14:45</td> <td class="xl68">1.30</td> <td class="xl68">1.30</td> <td class="xl68">1.30</td> <td class="xl68">1.30</td> <td class="xl65">E</td> <td class="xl65">2</td> <td class="xl65">POR</td> <td class="xl65">540</td> </tr> </tbody></table>

 

[Trebor76]

Hi there,

Though there's numerous postings like this (I know as I've answered heaps of them), try this...

Option Explicit
Sub Macro2()

    'http://www.mrexcel.com/forum/showthread.php?t=580211

    Dim lngRowStart As Long, _
        lngRowLast As Long, _
        lngRowActive As Long
    Dim rngDelRange As Range
    
    lngRowStart = 2 'Initial row number. Change to suit.
    
    lngRowLast = Range("A:K").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    Application.ScreenUpdating = False
    
        For lngRowActive = lngRowStart To lngRowLast
        
            'If the value in the current row of column G is: _
            1. less then 0.3 or _
            2. greater than 25 and _
            the value of the same row in column I is greater then 10, then...
            If Cells(lngRowActive, "G") < 0.3 Or Cells(lngRowActive, "G") > 25 And Cells(lngRowActive, "I") > 10 Then
                '...add it to the 'rngDelRange' range variable.
                If rngDelRange Is Nothing Then
                    Set rngDelRange = Cells(lngRowActive, "A")
                Else
                    Set rngDelRange = Union(rngDelRange, Cells(lngRowActive, "A"))
                End If
            End If
        Next lngRowActive
            
    'If the 'rngDelRange' range has been set, then...
    If Not rngDelRange Is Nothing Then
        '...delete the row(s) from it.
        rngDelRange.EntireRow.Delete xlShiftUp
    'Else...
    Else
        '...inform the user that no rows were deleted as _
        there were no matching criteria within the dataset.
        MsgBox "There were no rows deleted as no there were no entries matched the desired criteria.", vbExclamation, "Delete Row Editor"
    End If
        
    Application.ScreenUpdating = True
        
End Sub

...initially on a copy of your data in case the results are not as expected.

Regards,

Robert

 

[Rick Rothstein]

Here is a non-looping macro that should do what you asked for...

Sub TransposeData()
  Dim UnusedColumn As Long, LastRow As Long, A As Range
  Const StartRow As Long = 2
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  UnusedColumn = Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column + 1
  With Cells(StartRow, UnusedColumn).Resize(LastRow - StartRow + 1)
    .FormulaR1C1 = "=IF(OR(RC7<0.3,RC7>25,RC9>10),""X"","""")"
    .Value = .Value
    .SpecialCells(xlConstants).EntireRow.Delete
  End With
End Sub

 

[mlarson]

Rick and Trebor76, thank you very much! It worked perfectly. I think I'll probably use the non-looping version but it's good to have both macros to help me learn. Thanks again!

 

[mlarson]

When using Rick's code in the spreadsheet example it worked perfectly. However, using the same code in a different workbook returned the following error: Run-time error '1004': Application-defined or object-defined error.

I have the Macro saved as "TransposeData." When I run the macro is Workbook1 it works perfectly, but when it runs in Workbook2 it returns the above error. Any ideas? I use Windows 7 and all workbooks are Excel 2010.

 

[mlarson]

By the way, when I select the debugger it highlights the following:

With Cells(StartRow, UnusedColumn).Resize(LastRow - StartRow + 1)
</pre>

 

[mlarson]

I figured it out. It was producing the error because my 2nd workbook (where it didn't work) had the maximum of 1,048,576 rows.