Databases
StackOverflow2013
Postsdbo
PODeleteing rows without a loop meeting certain criteria
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PODeleteing rows without a loop meeting certain criteria
    primarykey
    data
    text
    <p>I am working on writing a macro that deletes all rows that are less than .75 from a value I found using a formula. In another thread, on here, I found a loop that works, but this takes a lot of time to run... so I am trying to find a way without a loop. So far, I have the code as seen below, but i get a "run-time error 1004, method 'range of object worksheet' failed" on the line </p> <pre><code>ws.Range(Left(rowsToDelete, Len(rowsToDelete) - 1)).Select </code></pre> <p>Anybody have any ideas on a correction? All help is appreciated</p> <pre><code>Private Sub CommandButton6_Click() Application.ScreenUpdating = False Dim ws As Worksheet Dim i&amp;, lr&amp;, rowsToDelete$, lookFor$, lookFor2$ '*!!!* set the condition for row deletion lookFor = "#VALUE!" lookFor2 = "0.75" Set ws = ThisWorkbook.Sheets("Entry") lr = ws.Range("H" &amp; Rows.Count).End(xlUp).row ReDim arr(0) For i = 1 To lr If StrComp(CStr(ws.Range("H" &amp; i).Text), lookFor, vbTextCompare) = 0 Or _ CDbl(ws.Range("H" &amp; i).Value) &lt; CDbl(lookFor2) Then ReDim Preserve arr(UBound(arr) + 1) arr(UBound(arr) - 1) = i End If Next i If UBound(arr) &gt; 0 Then ReDim Preserve arr(UBound(arr) - 1) For i = LBound(arr) To UBound(arr) rowsToDelete = rowsToDelete &amp; arr(i) &amp; ":" &amp; arr(i) &amp; "," Next i ws.Range(Left(rowsToDelete, Len(rowsToDelete) - 1)).Select Selection.Delete Shift:=xlUp lr = ws.Range("A" &amp; Rows.Count).End(xlUp).row ws.Range(lr &amp; ":" &amp; lr).Select Else Application.ScreenUpdating = True MsgBox "No more rows contain: " &amp; lookFor &amp; "or" &amp; lookFor2 &amp; ", therefore exiting" Exit Sub End If If Not Application.ScreenUpdating Then Application.ScreenUpdating = True Set ws = Nothing End Sub </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USMike
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. PODeleteing rows without a loop meeting certain criteria
      1. This table or related slice is empty.
      1. VTUpMod
    1. COyou could use specialcells to grab any formula error cells and delete the rows in one hit then use an autofilter and delete the .75 rows in another hit (using the .Text property is slow)
      singulars
      1. PODeleteing rows without a loop meeting certain criteria
      1. USJosieP
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload