AutoFilter VBA - Causes Excel To Freeze
Aug 6, 2013
This code does not work the way I expect it to. Presently it just causes Excel to freeze.
The idea is that if you double-click a cell within the CurrentRegion (starting at A1) that AutoFilters will be applied and the selection criteria will be the target value:
Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim MyRng As Range
Dim MyCol As Long
Set MyRng = ActiveSheet.Range("A1").CurrentRegion
MyCol = Target.Column
If Not Application.Intersect(Target, MyRng) Is Nothing Then
ActiveSheet.AutoFilterMode = False
MyRng.AutoFilter Field:=MyCol, Criteria1:=Target.Value, Operator:=xlFilterValues
Cancel = True
End If
End Sub
View 3 Replies
ADVERTISEMENT
Jun 1, 2012
I have a macro which I recorded, then modified. The first thing it does is to freeze the top row, then it goes looking for a particular row and inserts some formulae. Nothing complicated at all.
It WAS working perfectly, but I wanted some improvements in the insertion of the formulae. I got that working just fine, but now the panes freeze in the wrong place. No matter what I do, it freezes at cell I16. I want only the top row frozen.
Found this code which was reported to work:
PHP Code:
ActiveWindow.FreezePanes = False Range("A1").Select ActiveWindow.FreezePanes = True
It still freezes at I16. I have tried shutting down Excel, and even my computer, in case it is some weird bug where something is stuck in memory.
I tried recording another macro to format some cells and also freeze the top row. Same result.
PHP Code:
Sub wraptext_top_row()'' wraptext_top_row Macro''
Rows("1:1").Select With Selection
.HorizontalAlignment = xlCenter
[Code] ..........
Same result. I tried copying the contents of the worksheet to Notepad, then into a fresh workbook, just in case there's some weird hangup in formatting that I can't see.
There is NOTHING in the original code that references that cell, or even that row or column. NOTHING. How it got hung up on that one cell I cannot fathom.
The Freeze Panes command works normally if I apply it manually. Any cell, anywhere, it works as expected.
View 1 Replies
View Related
Dec 13, 2013
How to enable freeze panes, I am running excel 2003, I have no hidden workbook and I have followed the video that explains how to freeze panes, but it remains greyed out and I just cannot enable it..
View 1 Replies
View Related
May 28, 2014
I encountered a strange anomaly where a worksheet "freezes" when Application.DisplayFormulaBar = False. I can only replicate this in Excel 2013. Excel 2010, for example, works perfectly well, and as expected.
[Code].....
To replicate the anomaly (Excel 2013 only):
1. In a new workbook, insert an ActiveX command button on "Sheet1", no code required.
2. Run the following code
[Code] .....
3. Click on the command button.
4. Now click on any cell and try to enter a value.
Is your screen "frozen"? If so, go to another sheet, return to Sheet1 and try again. Does it work?
Here's an alternative code for MyTest() that causes no problems. Can spot the difference? Is there a reasonable explanation?
[Code] ....
Even more curious, call the following MyTest3 on Workbook_Open() and the workbook behaves. Run MyTest3 again and the screen starts freezing(!)
[Code] .....
View 3 Replies
View Related
Sep 25, 2012
How do I freeze the top 6 rows and the first 3 columns in excel 2010 work-sheet so when scrolling they remain static.
View 2 Replies
View Related
Feb 25, 2009
Is there a way to freeze your pivot table format after clicking for a ' Refresh'. I'm getting tired reformatting my column headers to wrap or in the middle etc. I'm working in Excel 2007.
View 6 Replies
View Related
Dec 17, 2012
I'm designing a form to look at data contained on a spreadsheet. I aiming for the user to select filter criteria which will then be used to reduce the data based on the criteria selected using autofilter. How can I set the filter criteria using a form to give the same options as when you manually select the filter using the arrows at the top of the row? Can I get the filter selections into a list box?
View 5 Replies
View Related
Nov 24, 2011
I'm using Excel 2007. I'd like to be able to autofilter on a particular value that I put in cell J5. How can I do that syntactically?
View 4 Replies
View Related
Apr 6, 2013
If I apply an autofilter in my worksheet the autofilter dropdownmenu stands in row 2 and not in row 1. How is this possible?
View 3 Replies
View Related
Jul 20, 2014
Is it possible to retrieve an arrary of the autofilter criteria. I know this is possible in pre 2007 but with 2007 onwards I can't find a way of doing it.
I know I can get all the available items in a list by using the scripting.dictionary and also by using the visible cells I could see what could be filtered but that is not really accurate for what I want.
E.G.
If I have multiple columns and look at the filtered information in one column I can retrieve an array of the visible cells from that column but that is not necessarily the criteria that is in the column. i.E. If another column has a filter rows may be filtered that would have otherwise been visible.
View 5 Replies
View Related
Apr 4, 2013
I have renamed some cells to custom names eg. renamed cell A1 to FLIGHT and B1 to FLIGHT2.
When I apply a filter to the columns and autofilter, the renamed cell now refers to a different cell! ie. FLIGHT no longer has the same text value in it
This also only happens when I autofilter by alphabetically or highest/lowest values
View 1 Replies
View Related
Mar 21, 2013
Code:
Selection.AutoFilter Field:=5, Criteria1:=Array("CHF", "DKK", "EUR", "GBP", "NOK", "SEK", "USD")
I am trying to use VBA to filter a list for not equal to. See line above. I want to filter a table I have for unknown Currencies basically.
View 1 Replies
View Related
Nov 4, 2013
I got a problem using AutoFilter with VBA in Excel.
It works well for regular filters, but filtering the date column does not work as intended. The column is formatted as date, I can filter it manually and absurdly, if I run my code, it filters nothing but when I check the filter and then only click ok (no change being applied to the filter criteria), it starts filtering correctly.
Here is my code:
Code:
ws.ListObjects(SheetName).Range.AutoFilter Field:=3, Criteria1 _
:=">" & CDate([datecell]), Operator:=xlAnd, Criteria2:= _
"
View 1 Replies
View Related
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
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
Jan 23, 2012
I'm working with three large data sets covering a month of data in 5 minute intervals. There is a single date and time column which I am using some custom formats to list Time in 12 hour format, AM or PM and Day of the week.
Based upon the various analysis scenarios, adding some Autofilter columns is meeting most of my needs. One issue I am having is that I can filter on a single day using the built in list that Autofilter provides. But I have been asked to look at weekend vs weekday.
Since Excel 2003 only offers two filter criteria, I thought I could use a custom filter with Saturday and Sunday as include or exclude criteria. But, what I am findings is that while the Autofilter shows each day in the dropdown, certain attempts to use them within the custom autofilter do not work.
For example, equals Saturday and equals Sunday correctly returns only weekend data. However, if I try changing both to Does not equals, the result is that no data is filtered.Some of my research indicates that the problem is that the date information being presented as days of the week would need to be text strings . This seems to shed some light on the issue as any attempts at using wildcards fail.
View 3 Replies
View Related
Jun 6, 2013
I have data that is formatted in an Excel 2010 table. The two columns in question are [Invoice #] and [Description]. In the description column I have descriptions of products as well as freight. The same invoice number would be tied to the product description as well as its associated freight. I need to filter out certain product types and their associated freight items.
The macro I wrote creates an array of invoice numbers that I want to filter out and leave the remaining invoices, but I can't get the filtering part to work. Here is my code:
Dim Invoices() As Variant 'array of invoice numbers
Dim Descriptions() As Variant 'array of Descriptions
Dim InvoiceFilter() As Variant 'array of invoice numbers to filter
Dim i As Integer 'counter
Dim j As Integer
j = 1
[Code]...
What happens now is that it filters out all values in the Invoice column instead of only the values in the InvoiceFilter array.
View 1 Replies
View Related
Sep 4, 2013
I really like the slicers in Excel when working with data scenarios, but I don't like having to scroll up and down through the list of options.
Is there a way to add a "Search" box at the top like the traditional drop-down auto-filter? Or, does any loophole to making one?
View 2 Replies
View Related
Nov 9, 2013
I am using Excel 2013. I have an autofilter turned on for the columns in the spreadsheet. When I click on the filter button at the top of a column, the filter values do not have any check boxes next to them so I can't check any values. how to fix it so that it has checkboxes? I've attached a screen shot.
View 2 Replies
View Related
Jul 21, 2014
How can I get the dropdown list to not include blank cells in the list reguardless if the sort filter is used or not.
Sheet 1 (STATS) is the dropdownlist Cell B12
Sheet 2 (Orders) is the data, I cant convert to tables because the cells are active and storted Column B2: is the data named.
so I have to use a formula to do this.
Using Excel 2010
See attached sample
Sample.xlsm
View 7 Replies
View Related
Dec 7, 2011
I was wondering if it is possible to have cells formatted based on the presence of an autofilter? I have a large worksheet, and to clean it up I would like to make the entire sheet (minus the category headers) invisible. Then, when the user applies a filter, the cells become visible.
View 1 Replies
View Related
Apr 28, 2014
I'm having a hard time making this maro work in Excel 2010.
I need it to filter out the items "AR", "BATCH", and the line of "Total:*" where the * is a total amount of any given number dependant on the day.
Below is the coding I have that Excel is not liking.
Sub FilterAccurateRawData()
'
' FilterAccurateRawData Macro
'
'
Rows("1:1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$AA$45415").AutoFilter Field:=1, Criteria1:=Array("<>AR", "<>BATCH", "<>Total:*")
Operator:=xlFilterValues
Sheets("Instructions").Select
Range("A9").Select
End Sub
View 3 Replies
View Related
Dec 17, 2007
I have multiple sheets as part of my excel calculations which refer to the first sheet in the group. Each one calculates different values for me based on the numbers I put into the sheet.
So to put in these numbers, I insert new rows to the start of the sheet which is what I always want it to calculate. Some of the calculations are comparative to other points from my original page. So I might ask it to go back and compare to 3 months ago or 6 months ago.
Long story short, when I insert those new rows, the formula on the other pages shift down to stay on the previous days data. How can I lock it in so it stays focused on just the cell I tell it to reguardless of how I move those cells around. So when i hit insert, I still want it focused on that sheets A1 and NOT A2.
I attempted to look up the help section but since I am having a hard time putting this into words, help was not much "help".
View 9 Replies
View Related
Jan 15, 2009
I tried highlighting the top 2 rows and clicking 'freeze panes' but it didn't seem to freeze correctly
View 3 Replies
View Related
Nov 20, 2009
Just wondering if I can lock a worksheet to display the content within the window only. Eg 22 Rows by 6 Columns? Therefore stopping people tampering or moving objects?
View 10 Replies
View Related
Nov 23, 2013
i want freeze only one cell. I have a button. I want keep that button permanently in a place. i am planning to freeze a cell where the button is located.
View 3 Replies
View Related
Nov 3, 2009
I'm trying to get one of my macros to freeze panes in several worksheets. For some reason though, sometimes it freezes the panes in the wrong place. What could be causing this? My code basically looks like this:
View 4 Replies
View Related
Jul 7, 2006
when ever it runs it brings up the print preview of the correct sheet (looks just like i wanted) but the vba object stays infront of the print preview window and locks up i cannot close it and cannot access the print preview window because it is blocked by the vba object even though i can see the print preview in the backbround.
i have tried asding "unload me" after the "activesheet.printpreview" line but does not make any differnce i end up having to close excel via task manager and reopen it.
Private Sub cmdPrtPreview_Click()
On Error Resume Next
Sheets("invoice").Select
ActiveSheet.PrintPreview
End Sub
View 5 Replies
View Related
Aug 20, 2014
I 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.
View 4 Replies
View Related
Oct 16, 2009
I am wanting to freeze the first column on my spreadsheet (easy) but also the top few rows. Every time I try (am using 2007) it seems to make a strange selection where it splits the screen exactly into 4 equal parts rather than just the first column and top 4 rows.
View 3 Replies
View Related