Delete Rows With AutoFilter Takes Forever

Jun 18, 2009

I have 3 subs.

The 1st sub populates a series of sheets with data present in a master sheet called Overall. I'm using ADO because it's fast...

The 2nd sub loops through a range of criteria (also happen to be sheet names) and autofilters a range using the array items as the criteria. The filtered rows are then deleted (excluding headers).

The 3rd sub simply runs 1 and 2.

The 1st sub completes almost instantly.
The 2nd sub is also darn quick if I run it BEFORE the 1st (which is not as intended).

I have tried compliling both into a single sub and still the whole autofilter bit takes ages.

View 9 Replies


ADVERTISEMENT

Copying Column With VBA Takes Forever

Feb 8, 2010

The workbook has two sheets, "user" and "analysis". I have this code in a button sub:

View 2 Replies View Related

Countif Formula Takes Forever

Oct 19, 2011

I have a formula as follows:- =COUNTIF(C$2:C2,C2)

The problem is I have to fire it down 35,000 rows and it takes forever and freezes the PC. Is there a VBA code that will do it quicker or do I have to put up with it?

View 3 Replies View Related

Index Small Formula Takes Forever To Save?

Sep 15, 2014

I have one data tab which constantly changes. (adding more products and corresponding volume/prices) For the lookup tab, I want to look up one value and return multiple values.

I have figured out an index array formula but it just calculates so slowly. My live file is about 8MB now. When I apply my formula, it takes forever to save.

View 7 Replies View Related

Delete Rows After AutoFilter

Jan 5, 2008

I use Excel 2007 and need to find the best way to delete rows selected after Auto-filter. This autofilter selects multiple criteria for a particular column. The closest post to do what I am looking for is: [url]

But this doesn't really help me firstly because it pops out an input box and asks to enter 1 criteria. I have multiple criteria for each column depending on which I'd like to delete rows.

Alternately, I have been trying this code belwow but its too time consuming for my 50,000 rows.

Sub CodeCleanup()
Dim r As Long
r = 65536
For I = 1 To r
If Cells(I, 30).Value = "R" Then
Rows(I).Delete
End If
Next I
End Sub

View 3 Replies View Related

Delete Blank Rows With Autofilter?

Jan 14, 2012

I have created a file where I use the Subtotal function. Once I collapse the information to only give me the Total, I would like to copy the Total rows into another worksheet. However, when I do this I get blank lines in between. I am trying to find a way to delete the blank rows in between the Total rows I need. Is there a way to do this with the auto filter function?

View 1 Replies View Related

Delete All Rows Hidden By Autofilter

Oct 3, 2007

I'm wondering if anyone has a answer to the problem of deleting all the rows that are hidden by an autofilter. We currently have a spreadsheet used within the office that catalogues all applications received, and we want to select all the applications that are relevant to a certain month with the autofilter and use a macro to delete those that are irrelevant, before emailing the spreadsheet to a client. Manually deleting all irrelevant rows would be time-consuming.

The current code I have is:

With Worksheets("Sheet1")
If .AutoFilterMode Then
With .AutoFilter.Filters(1)
If .On Then
Else: Rows.Delete
End If
End With
Else:
End If
End With

The theory is that the code first selects the relevant worksheet, determines whether autofilters are on, selects data that is being covered by the autofilter, indentifies those that is being displayed and does nothing, and identifies those which are not displayed and deletes them - in theory!

View 7 Replies View Related

AutoFilter VBA - Filter And Delete Unwanted Rows

Oct 25, 2012

I'm trying to filter and delete unwanted rows as I need row where dates is within a specified range.

How can I do this using AutoFilter? or are there any other alternatives?

View 2 Replies View Related

Autofilter And Delete In VBA

Feb 15, 2007

From my data set I would like to delete all rows that show "Yes" in Column I.

I copied this piece of code a few days ago from this site and have attempted to modify it eg by altering Columns to "I" and Autofilter Field to 9 and Criteria1 to = "Yes", but without success. Can you please help?

With Columns("A")
.AutoFilter Field:=1, Criteria1:=""
.Range("A2:A" & Rows.Count).EntireRow.Delete
.AutoFilter
End With

View 9 Replies View Related

Autofilter Conditional Delete And Copy?

Oct 25, 2012

Wondering if there's a macro that can do this when i press a button.

In spreadsheet "Complete Backlog"Autofilter onShow rows where WIP Status (Column K) equals "Closed"Cut rowsPaste into next available row of "Closed Jobs" spreadsheet. No overwrites.

Or you could switch step 5 and 6 so that the rows are copied over to "Closed Jobs" and then deleted from "Complete Backlog".

View 1 Replies View Related

Autofilter To Delete Chunks On Unwanted Data

Feb 13, 2009

I am using an autofilter to delete chunks on unwanted data, by using Range(Selection, Selection.End(xlUp)).Select however it picks up the header row. Is there anyway I can either get the selection of data to select one less line, or a way to get it to leave the header line?

View 2 Replies View Related

AutoFilter - Using Selectable Rows

Aug 7, 2007

If I create a pivot table, I'm able to click the arrow to the right on my row header, unselect "Show All" and then manually check boxes next to the rows I want to show. The pivot table then shows only the rows I'm interested in.

I can't figure out how to do this with filtering outside of the pivot table. I can select AutoFilter and then select "All", "Top 10" or individual rows, but I don't have the option to use a checkbox style selection to choose multiple, but not all, rows.

How can I set this up to have checkboxes associated with the AutoFilter dropdown so I can pick and choose which rows show?

View 9 Replies View Related

Vba Autofilter Non-contiguous Rows

Nov 15, 2009

I have a workbook that is used for storing test results that relate to people in different groups.

B4, C4, D4 are the surname, first name and group headings. This is what I want to filter by.

Column E is empty

F,G,H,I,J,K are the results.

Column L is empty

M is total of results

What I want to do is set an autofilter from B-L, only have drop downs on B,C,D. But in addition to this I want to have it that the range can dynamically change if another column of results is inserted.

To calculate my formulas in the worksheet I named/defined M4 as 'Total' so that I could then offset from this named cell to determine the range of test result columns, which works well. I just don't know how to do this in vba.

I have this to just get the autofilter running:

With ActiveSheet
.Unprotect ("password")
.AutoFilterMode = False
.Range("B4:L4").AutoFilter

.Protect ("password")
End With

I need the range to be B4: offset of the cell 'total' 0,-1

and I need to loop through from F - offset of the cell 'total' 0,-1 and turn the dropdown off on each of these headers.

View 9 Replies View Related

Number Rows With AutoFilter On

Oct 23, 2007

I am trying to number col A from 1-??? with auto filter on. The only way I know how to do this would be to type 1 in the top col and drag it down, however when autofilter on, this doesnt work. Is there a way to do this, with or w/o VBA?

View 8 Replies View Related

Hide Rows Based On Autofilter

May 7, 2014

I have a spreadsheet that is split in two parts , one with headers in row 16 with data flowing down to row 190. In row 192 I have another set of headers with data flowing down from that to row 300.

I have a userform (roughly at cell B13 in the attached) that filters the first block of data into either Company, Syndicate, EU Corporate or ALL. (ignore the other filters) which feature in range B18:B190

What I want it to do, is that when one of the three options is selected, ie Company, is that all rows from 193 down are hidden other than those that are also Company (in the test case there is just one row). The same is true for when Syndicate or EU Corporate are selected in the userform, and if the ALL is selected then none are hidden.

View 2 Replies View Related

Userform Autofilter Visible Rows

Aug 5, 2014

I have a Userform that it makes possible to step through the the spreadsheet. It works with Previous and Next buttons. The Userform opens with a doubleclick. This works fine, but when I use an autofilter and the criteria reduces the number of rows, the Userform shows the hidden rows as well as the filtered rows. How could it be made that it only steps through the visible rows?

View 2 Replies View Related

Userform Autofilter Visible Rows

Jul 11, 2007

I have a Userform that allows you to step through the the spread sheet that works with Previous and Next buttons. This works fine.

When I use an autofilter where the criteria reduces the number of rows the Userform shows the hidden rows as well as the filtered rows. how can I make it just show the visible rows.

View 14 Replies View Related

Autofilter - Ignore Non-visible Rows

Jan 20, 2014

I have a spreadsheet with over 20000 rows of data and have used autofilter to find the information I want to check. Once I have these rows I want to check whether the cells in Column AE are higher/lower than the number (in this case '3'). Once it finds a cell that meets this condition then it copies and pastes the whole row so that I can review the entry.

The problem I'm having is that it doesn't work all the time. I've tested it a number of times in break mode & played it throughout and it works as expected, whereas other times it picks up the top row (header) and duplicates it or it picks up hidden rows and pastes them in sheet 2 if the condition is met. I want it to just pick up rows if they are visible (using the filter) if the condition is met.

Here's what I have so far:

Code:

Sub Lvl3v2()
'Level 3
Dim i As Integer
Dim r As Range
Dim c As Range
 
i = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

[Code] .........

View 2 Replies View Related

AutoFilter By Rows (with Orientation - Left To Right)?

Jun 17, 2007

Is it possible to use AutoFilter by Rows (with Orientation - Left to Right)?

View 9 Replies View Related

Using Autofilter To Select A Series Of Rows

Aug 24, 2004

I intend to create reports from a master sheet.

The master sheet contains company information; each row represents a (company)record.

The first column contains the country code where the company resides, e.g. DE.

I dug up this piece of
For i = 12 To 1 Step -1
Set rngAutoFilter = Cells(1, i)
rngAutoFilter.AutoFilter Field:=1, Criteria1:="DE"
Range(rngAutoFilter, rngAutoFilter.End(xlDown)).SpecialCells(xlCellTypeVisible).Copy _
Destination:=Worksheets("Sheet2").Cells(1, i)
rngAutoFilter.AutoFilter
Next i

I found this code to work allright but, it only copies the singel cells containing "DE" and the topfirst row. At least that is what is the visible result.

How do I modify this code to copy EACH complete row containing "DE" in the first column?

View 9 Replies View Related

Count Visible Rows After AutoFilter

May 16, 2008

Is there a simple way to count unhidden rows? I saw Counting number of unhidden rows =SUBTOTAL(103,D11:D7180) and, frankly, I'm wondering if there is a way to do it without a formula. I don't need the count in a cell, per se, just a quick count of the unhidden rows of a worksheet for usage elsewhere.

View 5 Replies View Related

How To Display Number Of Visible Rows Using AutoFilter

Jul 12, 2014

I am trying to display a certain number of visible rows, using AutoFilter. My idea is to use a helper column that will 'number' each visible row, but I'm not quite sure what functions to use for this.

View 5 Replies View Related

Combining Hide Columns With Autofilter Rows

Dec 21, 2008

I have a spreadsheet in Excel 7 for a lifelong cashflow model. The main worksheet is called 'Projection' with ages in rows & income (from a variety of sources) and expenditure (of various kinds) in columns fed with data from the Input sheet.

On the 'Input' sheet I have a button called Format Graphs which controls the following macro:

View 6 Replies View Related

Randomly Choose Sample Rows After Using Autofilter

Nov 5, 2009

Can anyone explain clearly (using layman terms) about how to go about randomly choosing specific number of rows from a set of autofiltered records?

For e.g., If there are 1000 rows of data in a worksheet and after applying autofilter to certain column(s) [one or more] say i get some 75 rows. Then how can x no. of rows be chosen where x can be input by the user or calculated based on a certain percentage? Say if out of these 75 filtered rows, 8 rows have to be chosen randomly - where 8 can be input by the user or also be calculated as 10% of 75.

Also, is it possible to choose these x records from the filtered set of rows without actually copying them to another sheet?

View 12 Replies View Related

Return Count Of Visible Rows After Autofilter?

Mar 19, 2012

I have a work book.

In column C27 and down, the user can input a date.
In column M27 down, the user chooses pass or fail.

N8, contains a date chosen by user as the "From" date and P8 the "to" date.

Cell o11 is "Passed" and cell 012 is "failed"

The user can choose a date range and input the from and to date in N8 and P8, this will count the number of pass and fails and input the number in O11 and o12.

Formulas are below.

Code:

=COUNTIFS('Aff MFR'!C27:C1663,">="&'Aff MFR'!N8,'Aff MFR'!C27:C1663,"="&'Aff MFR'!N8,'Aff MFR'!C27:C1663,"

View 1 Replies View Related

Delete & Merge Columns,Delete Rows With Filter, Etc

Jul 15, 2009

1. Remove J,K,N,A Columns,

2. In the last O (TIMESTAMP) column, the date is 14-Jul-09 format change it to 07/14/2009 (this format mm/dd/yyy

3.Filter L column (VAL_INLAKH) Remove all rows from whole sheet which has 0 value

4. Column C (EXPIRY_DT) date format is 24-Sep-09 , "dd-Sep-09" change to "Sep" only

5.Merge Column B,C,D,E (SYMBOL.EXPIRY_DT.STRIKE_PR.OPTION_TYP
respectively )

View 3 Replies View Related

Consolidate Multiple Worksheets Into One - Copy Rows Even When Autofilter Is In Use

Aug 19, 2014

I have a workbook that has several sheets in it with various rows of data on each sheet. I would like to have a macro to loop through all the sheets in the workbook and copy and paste the rows into a new summary sheet. The rows to be copied should only have data in Column A, in other words if Column A of a row is blank I want it skipped. Also Row 1 of every sheet contains my headers, and I am only using columns A through M. It needs to be able to copy rows even when autofilter is in use.

View 13 Replies View Related

Excel 2007 :: Removing Blank Rows - Autofilter

Nov 8, 2011

I have several sheets with about 250,000 rows per sheet.

But, even I sort by Column A, there are STILL hundreds or rows that are total blank interspersed down the page . . . I can't autofilter for blanks because there is too much data . .

How can I get rid of them?

View 2 Replies View Related

Deleting Hidden Rows Based On Results Of Autofilter

May 23, 2007

I am performing an autofilter within a spreadsheet to display only those lines where a name exists in column A. Then I delete all hidden rows. I am having a problem when the autofilter results in no rows being visible. Here is the code I am using for the delete hidden rows:

On Error Resume Next ' In case there's no hidden cells

With Cells
Set rngHidden = .SpecialCells(xlCellTypeVisible)
.EntireRow.Hidden = False 'Unhide all cells
rngHidden.EntireRow.Hidden = True 'Hide previously visible cells
.SpecialCells(xlCellTypeVisible).EntireRow.Delete 'Delete previously hidden cells
rngHidden.EntireRow.Hidden = False ' Unhide previously visible cells
End With
End Sub

View 9 Replies View Related

AutoFilter Method Of Range Class Failed - Yet Autofilter Works.

Sep 25, 2009

Im sure this is a very common problem. I tried searching for it but I havent found anything that solves this for me. Here is the code Im using:

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved