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
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
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 RelatedWondering 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".
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!
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?
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 RelatedI 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.
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 RelatedI can select the top cell in column "F" after filtering by multiple columns using VBA and arrays, but now want to I want to use the top cell in column "F" to search for all other equipment that uses this item.
E.g. remove filter, and reapply autofilter to column "F" based on selected cell as per below VBA
Note: Row 1 contains command buttons and row 2 Headers.
I can set the autofilter, change it, remove all using Macros. What I need to know how to do is reset a filtered column to ALL while leaving all other filters on.
View 2 Replies View RelatedI would like a macro to find the columns named "apple" and "peach" and delete them. These would always be in row 1 but would always be in different column letters which is why I want the macro to simply find these columns by their name and not by their column letter.
And yes, I do mean the entire column altogether, shifting entire columns to the left. Wipe it off the face of the earth
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 )
auto filter options in excel. For some reason it is only sorting up to row 140.
View 14 Replies View RelatedI have an formula if statement that returns "deletethisrow" if the test is true.
For every occurence of "deletethisrow" I want to delete the row. The number instances will be variable each time I run the file. So maybe it will find that string, maybe it will find 10 instances. I want to do some kind of loop that won't error out when it cannot find "deletethisrow", but will delete the rows for each instance where it does find this string.
I know it was verbose, but if I just do a loop for a fixed number of loops it will error out if it runs out of rows to delete.
Say you have a long list of data, and you go to Data menu --> Filter --> AutoFilter
And then you want to use the Custom AutoFilter. Here's a screenshot:
[url]
Is there any way to do MORE than two autofilters?
I have a list of item numbers in a column which is Autofiltered. I can go to the autofilter drop down and then to custom and select two item numbers to allow through the filter, however I have about ten item numbers that I want to allow through the filter.
View 10 Replies View RelatedIs it possible to have multiple instances of autofilter on a single worksheet? The two autofilters should not be related to each other and are on different sets of data (in different rows as well as columns but in the same worksheet).
View 2 Replies View RelatedI have a situation - where I have a table and a "eSubtotal" cell that basically shows the subtotal value when Autofilter is ON and a SUMIFS calculated value when Autofilter is OFF. I have written this in the Selection change event of the sheet.
For this purpose, I have perform a regular check of AutoFilterMode = true or false and based on this result, I change the formula in the cell, eSubtotal.
Now the challenge is, I don't want to apply SUBTOTAL formula when AutoFilter is ON but there is no filter in any of the columns. I want to keep the SUMIFS just like that in this case.
So far, no good luck..
The following the code from the macro recorder. Is there a better way to execute this task in proper VBA code?
How do you add an [If...Then] critieria when 0 or 0.00 are not found, then continue to search for the next criteria, Non-Blanks?
I have 2 more questions within this .....
When I run the following code, it just returns records that matches "Commonwealth" and not a single record with "State". What am I doing wrong?
i am trying to filter data based on more than one criteria (8 to be precise). I have some data in one worksheet and i need to transfer it to other worksheets depending on certain criteria. for example if cell A1 has A or B then it should go to "temp1" spreadhseet, if A1 has C,D, E, F, G or H then it should go to "temp2" worksheet etc.
Is there a smart way of doing this rather than writing a number of with statements using 2 criterias each and hence copying data in more than one attempt (and thus slowing down the macro)?
I did think of using creating a dummy column, then using If statements to write True or false in that column, using true & false to filter and copy the data and then finally deleting the column. but as i understand i can not have more than 7 nested if statements but i have 8 criterias.
I have an autofiltered sheet, on which I need to find the last visible row (all columns have the same # of rows in use). I am trying to use:
View 4 Replies View RelatedCode runs rather slow. Is there a better way to achieve the same results?
View 5 Replies View RelatedI have a workbook of approx. 60,000 rows, with about 20 columns including a source identity column, such as 'Leeds' , 'Barnet' etc..
What i need is a solution that will auto filter all rows that have a value of 'Leeds' in the source column into a new workbook called 'leeds.xls' for eg. and so on (for each unique source value) and loop until the whole data set has been filtered.
Saves manually filtering, copying and pasting....over and over.....
Im guessing the VBA needs to build / look at an array etc...
This macro was working just fine, now it generates an error on the line in red.
View 2 Replies View RelatedI have an spead sheet with 9 sheets 1 master and 8 sub sheets. The master sheet runs for col A to AV and the it can have x amount of rows. At the moment i use the following but this takes an age.
View 9 Replies View RelatedThe attached file has two sheets, “order” sheet and “stock” sheet. Can some help me with the followings:
1-In either sheet, I would like a VBA where if I put the mouse on a cell and then click on a VBA button, the VBA performs an Auofilter of the cell I have selected.
2-In sheet “Orders”, I would like a VBA where if I put the mouse on a cell and then click the VBA button it takes me to sheet “stock” and performs a find function of the cell I choose in sheet “orders”. For examples, if I put the mouse on cell E21 in sheet “orders” and press the VBA button, it takes me to cell H297 in sheet “stock”.
3-In sheet “stock”, I would like a VBA where if I put the mouse on a cell and then click on a VBA button, it takes me to sheet “orders” and performs an Auofilter of the cell I choose in sheet “stock”
I have a code that will look in column C for all rows with data, turn on autofilter and copy the data into column A.
Then do the same in column D, paste into column A.
And finally the same in column E, paste into column A.
But I think the autofilter turning on and off might be a problem.
Code:
Sheets("PP").RAnge("A2:A50").ClearContents
Dim LastRow1 As Long
RAnge("C1").Select
[Code].....
How to achieve by formula what was selected in autofilter without use vba.
View 4 Replies View Related