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
ADVERTISEMENT
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
Sep 25, 2012
How do I delete filtered rows without deleting the hidden rows in excel 2010?
View 8 Replies
View Related
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
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
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
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
View Related
Oct 6, 2011
After red filling certain cells based on some conditional formatting, I apply a filter to hide them. Now I need to delete them using a macro script.
View 9 Replies
View Related
Jul 9, 2014
I am working on a file with different users entering information on different sheets (tracking the status of individual projects and activities). Using the macro below (which I found in the forums), the individual data will be combined on a master sheet. My problem is that some of the users will be using autofilters to filter their own data (for example--filtering to show only the "Open" projects). When I do the copy of the individual data, I want ALL of the data, not just the data that is being shown by the individual filter settings.
At first, I just turned the individual filters off before copying the data. The other users have asked if there is a way around this or alternatively, restore the filter values after the copy.
A key point, there are currently 8 user sheets. Each user is allowed to set their own filters and change them at will-e.g. one day, a user may filter on projects due in Q3, another day she may be looking at all projects closed in 2013.
VB:
Sheets("Combined").Select
ActiveWindow.SelectedSheets.Delete
Dim J As Integer
On Error Resume Next
[Code] .....
View 2 Replies
View Related
Feb 5, 2009
I have a formula to search a sheet for certain criteria and sum up the total, data is entered into this sheet by week number, where I then filter it for whichever week I need. I following formula works fine, but adds all the cells including hidden ones, how can i get it to ignore hidden cells??
=SUM(IF('FT Line 1-RX'!$C$2:$C$2100='FT costs 1-Rx'!B$1,IF('FT Line 1-RX'!$E$1:$GT$1='FT costs 1-Rx'!$A11,'FT Line 1-RX'!$E$2:$GT$2100)))
There is raw data in the 'FT Line 1-RX' sheet which is summed up in the 'FT costs 1-RX' sheet.
View 9 Replies
View Related
Aug 21, 2013
I just came across file that needs to be cleaned up. Problem is, the main summary references more than 50 hidden sheets for pay periods dating back to 2011.
I want to make everything values from previous pay periods then get rid of the hidden sheets.
View 4 Replies
View Related
Mar 4, 2014
I have an excel file with a table in it. It contains 2051 records (attached). This is just a sample, the original file has around 30,000 rows.
When I start using filters, I run into problems:
Step 1: Filter by Unit, condition (e.g.) Unit_23
Excel shows in the status bar the following message: 437 of 2050 records found.
Step 2: If I scroll to the bottom of the table, the row numbers are colored in blue (normal for a filtered list) however the last row is not colored and it actually should not be shown since its unit is not what I filtered for (its unit is Unit_25)
Step 3: Clear the filter of Unit
Step 4: Sometimes (depending on what I filter for), one or more of the bottom rows are hidden!!!
Attachment 301726
View 4 Replies
View Related
Apr 18, 2013
I have data in B4:B55 and need a formula to return a count of rows, including rows that are blank. However, there are hidden rows that need to be omitted from the count.
View 1 Replies
View Related
Jul 11, 2008
It would be great of I could delete all but one of the hidden worksheets. It would be perfect if I could tell the macro not to delete one of the hidden worksheets (called "Test O2 and CO2"), but if that is too difficult, then just one to delete all the hidden sheets would be fine.
View 9 Replies
View Related
Oct 6, 2007
I'm trying to delete all the Named Ranges in my excel document, but it appears there are some hidden ranges, and I can't delete them! How do you delete a range that is hidden? If I try to delete it with my code it says "That name is not valid." So I created a message box to see what the name of the range is.....and it's like 30 letters long! It starts like this..."={#N/A,#N/A, TRUE "Summary....." I woud like to attach my Excel file but it's too large. Below is my code
Sub Remove_Hidden_Names()
' Dimension variables.
Dim xName As Variant
' Loop once for each name in the workbook.
For Each xName In ActiveWorkbook.Names
If xName.Visible = True Then
xName.Delete
Else
MsgBox (xName)
xName.Delete
End If
Next xName
End Sub
View 5 Replies
View Related
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
Oct 15, 2008
I have working code to unhide specific worksheets from a large number of hidden worksheets and would like to be able to delete all the hidden sheets when the workbook is saved or maybe have a button to delete all the hidden (un-needed) worksheets beside the selection drop-down menu, so that only the selected sheets are saved.
View 4 Replies
View Related
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
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
May 29, 2014
I am trying to use the SUMIF function to create a formula that will read the visible cells in Column B and if any visible cells in Column B are equal to or greater than zero I want Excel to sum the corresponding values in the visible cells of Column A. Not so hard but I'm having a difficult time getting Excel to ignore data in hidden rows.
See attached file for and further explanation : Sample.xlsx
I've tried using SUMIF as well as the AGGREGATE function but no success, yet.
View 5 Replies
View Related
Jan 24, 2014
I have a spread sheet which has a number of protected cells so that they user can tab only on required cells to enter data. At times some of the rows are hidden however pressing "TAB" will continue to tab through on the cells within the Hidden Rows. Is it possible to only TAB through Cells which are visible?
View 1 Replies
View Related
Sep 5, 2009
I have a worksheet with a macro as follows: ...
View 6 Replies
View Related
Nov 19, 2006
I need a formula for counting rows. It should achieve the following;
It should count in increments of 1 (1,2,3,4, etc.) in each cell in a column (column AW, to be prescise).
It should skip hidden rows.
It should account for the fact that a formula is able to reveal rows and when this is
done, the counting formula should adjust to count the newly revealed row.
It should also be able to do the opposite - another formula/macro hides rows, and when this happens it should not count the newly hidden row.
I can imagine a formula in each cell of the column that says "Check the previous column and if it is visible, add 1. If a hidden row is encountered, do not add 1. When a non-hidden row is encountered again, continue adding 1."
View 9 Replies
View Related
Dec 15, 2009
I have a worksheet with hidden rows and Page Breaks. I know that the page breaks are causing my worksheet to print blank pages where I have page breaks and hidden rows.
I need the page breaks or the form just prints a mess... I tried eliminating them - but that didn't work at all.
I need a macro, which will examine the worksheet, look for the hidden rows, exclude them from the print area, and then Print the worksheet.
View 9 Replies
View Related
May 6, 2006
Why do I get a "sub or function not defined" error on the first line? The problem seemed to arise out of nowhere. The sub is located in module 1. I call it with "Call DeleteHiddenRows" in sheet1 inside of a "Private Sub Worksheet_SelectionChange(ByVal Target As Range)" event. Should I be adding some declaration somewhere (some "dim" line?)???
Sub DeleteHiddenRows()
For j = ActiveCell.SpecialCells(xlLastCell).Row To 1 Step -1
If Rows(j).Hidden Then
Rows(j).Hidden = False
Row(j).Activate
Selection.Delete
End If
Next j
End Sub
View 4 Replies
View Related
Jan 18, 2007
I'm completely at a loss I have a worksheet with hidden rows which I do not want to print yet Excel prints them anyway resulting in 12 pages rather than just the pages I want.
View 7 Replies
View Related
Mar 21, 2007
i count number of all rows with:
Dim countall As Long
countall = Sheet1. Range("a1", Sheet1.Range("A65535").End(xlUp)).Rows.count
this is the total of all rows, including hidden. how do i count the hidden rows which have been filtered?
preferably not a loop, isn't there a way to use "entirerow.hidden" and count that?
View 5 Replies
View Related
Jun 7, 2007
I am trying to create a macro to unhide only a row at a time, from around 150 hidden rows altogether. It is basically to give the effect of adding extra rows to a "table" that a user could then input with new data (I have to do this way according to other set-up in the workbook), but this isn't very important to do with this question. When I do a Format<Row<Unhide from the Excel menu, it always unhides ALL of the hidden rows. I have fiddled around but can't seem to find a way to avoid this happening, all I want is for just one row to unhide. I thought it might depend what cell/s I had highlighted, but I haven't found a way that makes it work.
View 7 Replies
View Related
Aug 21, 2007
I HID 4 rows using this help section. (Format - Hide)
Then, I modified several column widths simply by placing my curser at the top of the column and pulling it smaller or larger.
(I don't know if this did or did not cause my problem) which is simply that - I am now unable to find my hidden rows.
I know where they are supposed to be, but when I highlight them and click (Format - Unhide) nothing happens.
I tried (Find and Select - go to special) and no white bar shows up.
Can sombody please tell this frustrated Newby How to find the rows I just hid 1 hour ago?
If I posted in the wrong place, I appologise as I spent more time trying to figure out where and how to post than I did trying to solve my hidden row problem.
View 5 Replies
View Related
Sep 10, 2007
Is there a way to keep the Sum Funtion from adding in the values from Rows you have hidden? I want my total to be the result of only the visible lines, but have reasons for hiding rather than deleting rows (I may have to unhide some later depending on other factors)
View 9 Replies
View Related