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

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

Excel 2010 :: Delete Filtered Rows Without Deleting The Hidden Rows?

Sep 25, 2012

How do I delete filtered rows without deleting the hidden rows in excel 2010?

View 8 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

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

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 View Related

Macro Script To Delete All Rows Hidden Using Conditional Formatting Color

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

VBA - Copying Hidden Cells Without Turning Off Autofilter

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

How Do I Create A SUMIF To Ignore Cells Hidden With Autofilter

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

VBA To Delete All Hidden Sheets?

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

Error In Filtering Table - Additional Rows Shown / Rows Hidden When Filter Removed

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

Count Rows Including Blanks Omitting Hidden Rows

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

Macro To Delete Hidden Worksheets

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

Delete Hidden Named Ranges

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

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

Delete All Hidden Worksheets When Workbook Saved

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

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

Using SUMIF With Hidden Rows

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

Tab Stops On Hidden Rows

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

Unhide Hidden Rows..

Sep 5, 2009

I have a worksheet with a macro as follows: ...

View 6 Replies View Related

How Do I Count Non-hidden Rows

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

Don't Print Hidden Rows

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

Deleting Hidden Rows

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

Hidden Rows Are Printing

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

Count Hidden Rows

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

Unhide One Row From Many Hidden Rows

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

Unhide Hidden Rows

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

Sum, Ignore Hidden Rows

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







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