Databases
StackOverflow2013
Postsdbo
POstoring a worksheet in an array and working on them
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POstoring a worksheet in an array and working on them
    primarykey
    data
    text
    <p>the below code allows me to delete rows based which do not match my criteria, now the problem is it takes around 52 minutes as my rows exceed 1,68,000 and this will keep increasing on a weekly basis. now i was using filters to reduce the data based on my cnf and condition since the date range cannot be filtered out. But that isn't an option any more. i have to delete rows based on my date compare range. it seems array is my last resort but i do not know how to store my worksheet in an array and work on them. Could somebody please guide me ?</p> <pre><code>[CODE] ' to delete data not meeting criteria Worksheets("Dashboard").Activate n1 = Range("n1") n2 = Range("n2") Worksheets("Temp Calc").Activate lastrow = Cells(Rows.Count, 1).End(xlUp).Row For z = lastrow To 2 Step -1 If Cells(z, 6).Value = "CNF" Or Cells(z, 4).Value &lt;= n1 Or Cells(z,3).Value &gt;= n2 Then Rows(z).Delete End If Next z [/CODE] </code></pre> <p>this is something basic is was trying, keep i mind i know very little about array so the following may be wrong and incomplete</p> <pre><code>Dim arr1(), dim arr2() as variant lastrow = cells(Rows.count,1).End(XlUp).Row lastcol = cells(1,column.count).End(xlRight).Column arr1(lastrow,lastcol) &lt;- i dont know if this is correct &lt;------how do i copy paste my data into the array-----&gt; &lt;this is what i came up with for deleting what i dont need.&gt; For x=lastrow to 2 Step -1 If arr1(x,6)&lt;&gt;"" or arr1(x,6)&lt;&gt;"CNF" And arr(x,4)&gt;=n1 And arr(x,3)&lt;=n2 then For k = lastrow to 2 &lt;i dont know how to delete rows in an array rows(x).delete ? </code></pre> <p>i suck at arrays. Any help is appreciated </p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USmathew
    plurals
    1. PL
      singulars
      1. LTLinked
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POstoring a worksheet in an array and working on them
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POstoring a worksheet in an array and working on them
      1. This table or related slice is empty.
      1. VTUpMod
    1. COIf you want the array approach then we can do that but what problems did you find with Autofilter?
      singulars
      1. POstoring a worksheet in an array and working on them
      1. USSiddharth Rout
    2. COfilter was awesome but when my delete criteria was only blank cells and "CNF" but now i have to delete data based on dates.it does not filter my date ranges
      singulars
      1. POstoring a worksheet in an array and working on them
      1. USmathew
    3. COMay I see a sample data? just couple of rows... you can create a sample file and upload it in wikisend.com and share the link here. We will try the Autofilter approach first and then move on to Arrays if it doesn't work
      singulars
      1. POstoring a worksheet in an array and working on them
      1. USSiddharth Rout
 

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