Delete Filtered Data, Leave Headers
Apr 30, 2009
I have used the snippet of code below many times to delete rows of filtered data. For example, if I want to delete all rows containing "Y" in column F, this code would filter to that criteria, offset it to save the column headings, resize it to avoid deleting the row below the range, then delete the rows. This works beautifully, until I ran into an instance where there were NO "Y"'s in column F. Then it deleted the column headings.
How would I modify the code below to leave the headings in row 1, but delete any other filtered data? (Also, if there's a better way to delete filtered rows but leave headings, I would be interested.)
View 4 Replies
ADVERTISEMENT
Apr 2, 2014
Essentially I want to show how many loans funded in October 2013, from ABC Lending, and I want that result to show in cell K9 of the Sample Output sheet. However, since I'm dealing with filtered data, I cannot figure out the correct combination of formulas to make this happen (e.g., SUBTOTAL, VLOOKUP).
Specifically, I'm stuck with the combination of a) counting the results that meet the monthly date criteria, combined with b) the filtered results. The single cell that I want to solve is K9, on the attached "Sample Output" sheet. Once I see how this cell works, I can figure out the rest. This cell is looking for the number of loans that funded in October 2013, from ABC Lending.
The filtered results are located on the "SCORE Data" sheet. The filter is already configured to only show results for ABC Lending, and the relevant column is labeled "Funded" (column "I"). Since the "Sample Output" sheet is looking for loans that funded in that month, and not a specific date, I created column "U" on the "SCORE Data" sheet, to convert specific day-dates to month-dates.
View 3 Replies
View Related
Sep 28, 2009
I need to delete all characters BUT numbers (including decimals) in my sheet. Does anyone know how I can do this? I can do it either by using the find/replace menu, or use vba.
View 6 Replies
View Related
Apr 22, 2014
I've got a spreadsheet that's basically a large list of numbers, both whole and decimal. For example, let's say this is in cells A1-A5:
4
0.65
1.34
3
8.2
Is there a formula to get rid of all of the whole numbers but leave the decimals? (What I mean by that is I don't need 4 or 3 as they're whole, but I need the decimals to be left alone).
I know it's probably a really awkward question but I have over 2,000 lines to go through, it will take a long time to do manually.
Perhaps if it's not possible to a formula to delete entries, maybe just make all whole numbers say something like "NO", so that I can sort the column in A-Z order and delete all of the 'NO's quickly by highlighting them all together.
View 5 Replies
View Related
Apr 27, 2012
I am using Excel 2010 and I need a macro to delete all text from cells and leave the dates.
Below is an example of the contents of six cells. Some have just text, but some have text and dates.
1st Time Attender: 4/8/20121st Time Attender:1st Time Attender: 4/5/20122nd Time Attender:2nd Time Attender: 4/8/20122nd Time Attender: 4/5/2012
In this example, I would want the new cell contents to be:
4/8/2012
4/5/2012
4/8/2012
4/5/2012
View 1 Replies
View Related
Feb 27, 2008
I need to create a leave tracker wherein i will get the names of the person who want to take leave in that particular month. I have prepared a calendar to make an entry. I need to take care of the following things
1) The person must have sufficient leave balance available to take the leave. so i have to first check if he/she has sufficient leave balance.
2) There are four team leaders and each team has around 13-14 members. so if lots of people from the same team apply for the leave then they will not be eligible for leave. this i want to know in percentage as to how much percentage of people are taking leave from that particular team.
View 5 Replies
View Related
Feb 15, 2009
I use a filter to isolate one particular row of data. This unique row of data is data to be updated. There is ever only one record filtered, and that record is dynamic from the database of over 500 rows. Row 1 is the header. The filtered row is displayed under row 1 (physical row 2), but can be any of the row numbers according to the filter results. (ie the filter has extracted record (row) 13, and is displayed on physical row 2 below the headers)
How can I delete this filtered row. I can't simply
rows("2:2").Select
Selection.Delete
as this deletes record (data row) 2, not the 2nd physical row. I need to delete 2nd physical row regardless of the value determined by the filter.
View 9 Replies
View Related
Jun 24, 2014
Due to work, I need to creat the captioned file for our company.
View 2 Replies
View Related
Oct 3, 2008
Can't quite figure out how to do this. I'm looking to create a macro that will delete every other row of a filtered set of data. for example, if these are the filtered rows:
row 21
row 22 delete
row 50
row 51 delete
row 58
row 59 delete.
... and so forth down to the bottom of the filter. I cant seem to record the macro using a relative reference with the filtered data.
View 3 Replies
View Related
Apr 14, 2012
I have recorded a macro which deletes the visible rows after an auto filter is run - ( auto filter is for custom and blanks).
I thought this was working, but when i look at the code, it looks as if the rows to delete have been hard coded in, and not adapting to just delete the filtered ones.
View 8 Replies
View Related
Aug 19, 2013
So I have the following code:
'Remove UPC sales below $5000
Range("G38").Select
ActiveCell.FormulaR1C1 = "=SUMIF(C[-3],RC[-3],C[-2])"
[Code]....
View 2 Replies
View Related
May 11, 2012
I can see that I'm not allowed to delete rows from a list when it's filtered. Do I have any options to avoid this restriction?
View 2 Replies
View Related
May 6, 2014
What is required to delete the visible rows post filtering?
ActiveSheet.ListObjects("Table24").Range.AutoFilter Field:=9, Criteria1:= _
"Other"
'insert code to delete visible rows
ActiveSheet.AutoFilter.Range.Offset(4).SpecialCells(xlCellTypeVisible).Cells(1, 2).Select
ActiveSheet.AutoFilter.Range.Offset(4).SpecialCells(xlCellTypeVisible).Select
ActiveSheet.AutoFilterMode = False
Selection.Delete Shift:=xlUp 'this is where the error is occuring.
End Sub
View 3 Replies
View Related
Jan 30, 2013
how to delete only visible rows in a filtered list?
View 9 Replies
View Related
Apr 1, 2009
I have a large *.tab file with around 450,000 records. I opened the file in excel and then filtered two columns where there are values equal to 0.
I then highlight all the rows and try to delete them, but I receive the following error popup box:
"Microsoft Excel cannot create or use the data range reference because it is too complex."
How can I delete all these filtered = 0 rows without getting this error??
View 9 Replies
View Related
Dec 11, 2012
I have a range that has been formatted as a table. Once I've applied a filter, if I try to select all of the rows, and delete the visible rows the option is greyed out.
Is there no way, short of vba, that I can just simply delete the visible rows?
View 1 Replies
View Related
Aug 19, 2013
Is it possible to copy data that has been filtered on one tab into filtered data on another tab? I've attached an example, i'm trying to copy the values from column C on the 'From' tab to column D on the 'To' tab. I think the data is always going to be an exact fit in terms of the number of cells copied from and to.
View 1 Replies
View Related
Jun 6, 2013
I'm working through a filter macro to delete unecessary rows of data from my dataset.
- I have a Dynamic Range for my dataset called "CanadaData"
- I'm trying to delete rows from the 5th column of my dataset for cells containing "DIRECTSHIP"
The macro filters the range fine, but when if comes to deleting the row, the macro stops.
Sub CanadaWarehouseFilter()
x = Range("E" & Rows.Count).End(xlUp).Row
If Application.WorksheetFunction.CountIf(Range("E22:E" & x), "DIRECTSHIP") > 0 Then
With Range("CanadaData")
[Code] ......
View 2 Replies
View Related
Dec 24, 2009
What i want to do is clear some data within a merged cell but leave the rest.
I have added a sample workbook. The data i want to clear is highlighted in Red so the 11111 will be replaced with ...... and the 09/12/09 will also be replaced with .......
View 7 Replies
View Related
Dec 29, 2006
I have an Excel file with over 5000 rows all containing URLs
I only need the URLs containing a particular word in ther such as
if the URL contains ".edu" leave it, and if not, delete the data.
Is there a way to do this by VBA?
View 9 Replies
View Related
Nov 20, 2009
I have student demographic data, then I have to track how each student did on each question, but each question also corresponds to a reading standard and I need to pull two pieces of info from that. How they scored on the assessment (# of answers correct), and if they missed a certain standard twice or more (There are 4 instances of StandardA in the test and they had wrong answers on 2 or more of them). I have the solution to the first part. I entered the results of each question as a 1 for correct and 2 for incorrect then did a countif and only counted the 1s. I am stumped on getting an analysis of how each student did on the indicators. Since the indicators can occur more than once, generating a pviot table doesn't work in this instance, it'll count of 1s and 2s for StandardA, StandardA2, StandardA3, etc. and not the counts of 1s and 2 for all instances of StandardA.
Example:
What I want:
For student Bob - Number of times he answered incorrectly StandardA, StandardB, StandardC, StandardD, etc.............
View 3 Replies
View Related
Jan 24, 2009
I have a table with three headers:
Types: close to 4,000 total cells in the column with multiple repeats
Amounts: Obvious
Names: Only 6 available names (i.e. Tom, Bill, Fred, Richard, Sam, Alex)
It looks like this:
Type Amount Name
Type 1 | $$$$ | Tom
Type 1 | $$$$ | Bill
Type 2 | $$$$ | Fred
Type 3 | $$$$ | Richard
Type 3 | $$$$ | Tom
Type 3 | $$$$ | Sam
Type 3 | $$$$ | Alex
Type 4 | $$$$ | Fred
What I want to do is create a table with the parameters using the information contained in the previous table:
Type Tom Bill Fred Richard Sam Alex
Type 1 | $$$$ $$$$ $$$$ $$$$ $$$$ $$$$
Type 2 | $$$$ $$$$ $$$$ $$$$ $$$$ $$$$
Type 3 | $$$$ $$$$ $$$$ $$$$ $$$$ $$$$
Type 4 | $$$$ $$$$ $$$$ $$$$ $$$$ $$$$
Is there any way to convert the first table to the second table? I'm using Mac OS/X
View 9 Replies
View Related
Jun 7, 2014
I have a userform with two listboxes in them, i have set the columns in the listbox to 9 and would like to populate the column headers in the listboxes with the column header of one of the sheets.
View 5 Replies
View Related
Apr 10, 2014
I currently have a worksheet filled with data from other worksheets. I want to combine data from different columns when they share an identical row header and don't know how. For example:
1b--
1--c
1-d-
2-x-
2y--
2--z
Would go to:
1bdc
2yxz
Another specific issue is that some row headers are bold and need to be considered a separate header from the non-bold alternative.
View 4 Replies
View Related
Dec 30, 2013
I have a multi tabbed page and the page i want to sort (2) is looking up the 1st page and creating one column list from cell a1 to a280 and it includes a number of zero's from the look up pages because that is the correct result. I already have it sorting but I want it to sort and leave the zeros at the bottom (they are currently at the top) of the sheet so I can just print the info I need.
Can I create this in sort or do I need a macro and how can i write it as I am very green on VB.
View 14 Replies
View Related
Jan 23, 2014
I have uploaded a worksheet that has a macro attached to button 4,
I want the macro to search the header in "All Data" worksheet and transfer the data under them to under the same headers in "Quote" worksheet.
The data in "All Data" could be upto 60 rows of data.
quote test 1.xlsx
View 6 Replies
View Related
Feb 7, 2014
I have a DATA sheet that is all of the information reported on a monthly basis. This format will never change, but the data will. I have a second sheet called MMUSAGE.
On the MMUSAGE tab, I have identified which headers (in red) from the DATA tab that correspond with the headers in the MMUSAGE tab. Question is: how do I move the data from the DATA tab over to the MMUSAGE tab under the appropriate headers?
Example: IF Product Number on Worksheet(DATA) = Product Code on Worksheet(MMUSAGE) then move all data to Column L.
View 4 Replies
View Related
Feb 22, 2009
what i want to do is use 2 sets of data ss is attached to help understand
i would like to compare hometeam v player
therefore any games that steven gerrard didnt play in ( in the example )
it would leave all the games on sheet 3 where he wasnt involved.
hometeam has all the games liverpool involved in
player has all the games steven gerrard involved in
cross reference the 2 to leave all the games he wasnt involved in on a new tab
must be possible just not sure if i need look up or a macro
View 7 Replies
View Related
May 9, 2009
I'm using a formula that I got from a previously made excel sheet. The formula does what I need it to do and looks like this: =INT((D10-10)/2)
The problem is, if I don't enter a number in the cell D10, all the cells with this formula show -5.
If I don't want data entered into D10 yet, I'd like all the cells with that formula to be blank until I actually enter a number in D10.
I think it has to do with using an IF statement followed with ""? Am I on the right track?
Also, if I have other formulas like =SUM(AP3:AQ6), but the cells it refers to are blank, how can I make the cell with the formula also be blank rather than show a 0?
I know I can turn off the "Show a zero in cells that have zero value" option, but I was wondering how to do it with the formula instead.
View 9 Replies
View Related
May 9, 2009
I have 2 similar question.
I'm using a formula that I got from a previously made excel sheet. The formula does what I need it to do and looks like this:
=INT((D10-10)/2)
The problem is, if I don't enter a number in the cell D10, all the cells with this formula show -5.
If I don't want data entered into D10 yet, I'd like all the cells with that formula to be blank until I actually enter a number in D10.
I think it has to do with using an IF statement followed with ""? Am I on the right track?
Also, if I have other formulas like =SUM(AP3:AQ6), but the cells it refers to are blank, how can I make the cell with the formula also be blank rather than show a 0?
I know I can turn off the "Show a zero in cells that have zero value" option, but I was wondering how to do it with the formula instead.
View 9 Replies
View Related