I have an auto filter setup on some data. When I perform an advanced filter either in-place or to another location, the auto filter disappears. Is it supposed to be have like that? If so is there some sort of work around?
View 2 Repliesis there any way to get an advanced filter to play with autofilters? at the moment after i have applied the advanced filter it completly messes up with my autofilter to the extent that it moves where the autofilter actually sits and then when i try and use the autofilter it completly ignores the advanced filter i just applied. how to make the 2 filters play nice together and actually be able to autofilter the results of an advanced filter.?
View 2 Replies View RelatedIs there a way that I can tell my VBA code to select the first row that my advancedfilter is showing? The row number changes, depending on the criteria selected. I need to be able to select this row, so I can hide the first row the filter is showing. I tried an offset command, but that just selects one of the rows hidden by the filter.
View 9 Replies View RelatedI am trying to use the advanced filter code to move all unique items from one sheet to another sheet. For example, I have two sheets named (1) "data" and (2) "output". I want to filter a list of unique items only from the "data" sheet and copy the data to column A in the "output" sheet. The code posted works fine if I keep the unique data in the same sheet, but I can't figure out how to copy to the "output" sheet.
View 2 Replies View RelatedI'd like to say that this Forum is helping me very much when reading so many useful topics, get help from ppl here. From nothing I advanced my program very much. About my problem here:
I have an Excel Column with Dates (dd-mmmm-yy) and an UserForm/ComboBox. I'd like to add only the unique dates from the Excel Column to the ComboBox. The Excel Column Range is updating with new dates.
I tried to use RowSource but i don't know how to get the unique dates with RowSource. Maybe RowSource is not a good decision.
I need to transpose into 2x2 range to use as criteria for advanced filter
I tried
arrCrit = Array("Accnt_Nmbr", "313000", "DT", "RV")
Set rngCrit = wsData.Range("IU1:IV2")
rngCrit = WorksheetFunction.Transpose(arrCrit)
Where Accnt_Nmbr and DT are Column Headers in the data to to filtered
However when I run the output includes all DT's (Document Types)
How can I expand so both criteria are used in the filter?
I'm in the process of debugging some code at the moment and so have removed the On Error Resume Next's that I had in there.
I've since gotten rid of this where it wasn't necessary, but I have a general question about using the ShowAllData method with Advanced filter.
When I use:
ActiveSheet.ShowAllData
when there is no advanced filter applied, I get an error saying the method of Worksheet class failed.
I have searched and cannot find a way to check in VBA if there are advanced filters applied, though I can find code that will check if AutoFilters are applied. This doesn't work with AdvancedFilters.
I am trying to use the advanced filter with a not equal to operator, where the requirement is that the output not match an exact text string. I have tried <> and <>"???" (where ??? is the text string) and neither filter is working.
View 2 Replies View RelatedI begin in the Word of VBA. It's the first time I've a formula which seems necessitate VBA to be written down.
Before to try to execute my complicate macro, I've begun with simple tricks. But I've got a strange comportment of Excel :
I've the following Sub :
I have created an Excel spreadsheet with data. I froze the top row and added filters. This document will be used by numerous people, so I would like instructions for them on how to update the data on the same page as the data.
I put the text box off to the right of the data, but when the data is filtered, the text box is hidden along with the hidden rows.
Is there a way to have instructions to the right of the data that will not be hidden when applying filters? It does not have to be a text box but it does need to be on the same tab.
i got the userform to popup when the workbook is open and the buttons work fine... only problem is when i click on a button, it will ask the user yes or no... if the user press yes, it performs the required macro without any problem... but when the user press no, not only it will close the question, but also closes the userform... I would like the 'no' to close the question but not the userform...
View 4 Replies View RelatedI have a chart that I just added some dynamic text boxes to. That is the text is linked to a cell so that the text is automatically updated.
I also have a scroll bar that is used to adjust one of the cells. However, now once I click on the scroll bar it just disappears. I can click on the space where it is located and it will appear for a split second. Also, if I scroll down in the worksheet and then back up over the scroll bar the image will be refreshed and I can see the scroll bar again.
I didn’t have this problem until I added the dynamic text boxes, it worked fine with static text boxes.
Does anyone have any idea how to get around this, or do I have to stick with the static text boxes?
I realize that there are 100 posts asking this same question but I believe this question is unique. My actual data if far more complex, but I have attached a simple workbook to illustrate my problem. It is my understanding that the following code will generate a unique list of values. However, it does not work if an AdvancedFilter has been used previously in the code.
Sheets("Sheet1"). Range("B1:B6").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Sheets("Sheet2").Range("E1"), _
Unique:=True
I have been able to solve this problem by adding "CriteriaRange:=vbNullString" to the above code.
Sheets("Sheet1").Range("B1:B6").AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=vbNullString, _
CopyToRange:=Sheets("Sheet3").Range("E1"), _
Unique:=True
Here is my question: Do you need to reset the value of "CriteriaRange" each time you use AdvancedFilter, and if so, is setting CriteriaRange to vbNullString the best way to do so.
I am using advanced filter. When I use
Range("S3").Value = "" = "M6" = """"""
I get a runtime error 13. Am I missing something? After reading through other posts, I could not find anything wrong with this.
Whenever word wrap is applied the text is not visable in the cell. It
remains visable only in the bar above.
I have already confirmed autofit and it reduces the column to 1 character
wide.
I tried detect and repair on the installation and nothing.
I even upgraded from Excel 2k to Excel 2003 and no change.
I made a template file with a userform included to enter data into a protected sheet at the command of a button. When I open the template, everything works fine. However, when I use a macro to create a new workbook and import the template to create a new sheet (or several) the userform suddenly disappears! The template is saved as macro-enabled and the new workbook is saved the same way. What am I missing that I'm loosing the userform??
View 9 Replies View RelatedMy Workbook contains the following macro in Sheet 1, which displays a pop-up calendar in L15 when that cell is selected:
Private Sub Calendar1_Click()
ActiveCell.Value = CDbl(Calendar1.Value)
ActiveCell.Select
Calendar1.Visible = False
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Range("L15"), Target) Is Nothing Then
Calendar1.Left = Target.Left + Target.Width - Calendar1.Width
Calendar1.Top = Target.Top + Target.Height
Calendar1.Visible = True
' select Today's date in the Calendar
Calendar1.Value = Date
ElseIf Calendar1.Visible Then Calendar1.Visible = False
End If
End Sub.........
I'm having with creating a macro to filter some data from one sheet to another. I used the "record a macro" function to get the code I've posted below but part of the code is always changing.
In my workbook I have a main sheet called "Data" and another sheet called "Parts". The code below does what I need it to do, but the problem is that the cell range (A2:I139) is not constant and may change depending on how many rows of information I import on the "Data" sheet.
What I'd like to have happen is either have a dialog box pop up and prompt me to select the cell range (I've tried using the Application.InputBox function but can't figure it out), or since the first cell will always be A2 and the last column will always be I, have excel automatically figure out the last row that contains data.
In Excel I recorded a macro by carrying out some operations and then copied the code into VBA code window to the click event of a button expecting the code would operate well but in vain. What is wrong with my operations? By cliking command button placed on a userform I want to copy the date of one column to another and the data thus copied into another column should contain only unique value.
View 4 Replies View RelatedI'm using this macro to retrieve data from a worksheet within the same workbook. As you can see, I've set the destination range between row 12 and row 20. The problem is this code clears all text (not other formatting) from row 12 down even farther than the row 800 that is the end of my Origin range. QUE: How do you limit the reach of the ClearContents code?
Origin Name Range Definition
= 'ORDERS 1st QTR 08'!$A$1:$J$800
Sub GetData()
Application. ScreenUpdating = False
Range("A12:J20").ClearContents
Range("Data_Table"). AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
Range("M1:M2"), CopyToRange:=Range("A12"), Unique:=False
Application.ScreenUpdating = True
End Sub
I am trying to achieve the following and am not certain how to achieve this in Excel 2003.
1. make the Criteria Range Dynamic in Advance Excel, so that the Filter automatically updates on every Criteria change.
2. Raw Data range is kept in Closed workbook
3. Filtered Result displayed in another Workbook.
I have a large spreadsheet with many cells that have a drop down list using data validation. The list is a named range with no blanks. Most of the cells show the default I have set. However, sometimes when changes occur in cells next to one with a drop down list, the default value mysteriously disappears and the cell appears blank. The functioning of the drop down lists is intact, it's just that the cell appears blank.
View 5 Replies View RelatedI add data validation into 4 columns and when i re-open the spread sheet after saving it, it clears all validation.
Although it clears the validation, the place where it gets the data from is still there. i have also unfroze panes and is still clearing data validation
Attached is the spread sheet. The data validation is in columns A, G, H and I on the 'unsourced' page and is getting the data frfom the 'Sheet1' Page.
using Office Professional Plus (2013). Within my spreadsheet, I have a series of progressive dates to be used in as a timeline. Ultimately, what I need to accomplish is that when I change one date, all the proceeding entries update according to my timeline criteria (in this case 3 weeks or 21 days). I created a SUM formula that worked but only calculated one time. Once the calculation completes, the cell drops the formula. I need this to be constant as the dates often change. How do I get the formula to maintain? Further more, is there a better method to accomplish the task at hand other than the SUM option?
View 7 Replies View RelatedI have the same problem as Paul C in the following post:
(Pictures disappearing)
"Hi not a VBA problem, but when i insert pictures or logos into my workbook they disappear, i can insert a picture close the workbook but when i re-open it the picture has gone, i have to click on where it should be to get the border to appear, the move it to get it to appear.
I have tried inserting BPMS, JPGS, GIFS, but i have the same problem with them all, I have used the bring forward command but still have the same problem, I have even tried re-installing Office 2003 and installing all the latest updates but still get the same problem, any ideas?"
I have tried:
- Tools > Options > View > Objects > Show All
- Changing macro security level
- disabling macro security in my antivirus
On a sheet, I have a Worksheet_change sub that checks if the cell changed is in column 1, 10, or 19 and if it is, then the six cells to its right are filled with formulas I need. All formulas work, except for one. I've checked over and over again and the formula itself works fine - the coding behind it is exactly what it's supposed to be.
But for some reason sometimes the formula isn't put into the cell. I haven't found any pattern yet with this problem. Even stranger is that the formula is identical to one in another column before (which is also entered by the macro) and the other one has never disappeared yet. I'm setting both formulas with the abc.FormulaR1C1 property (I don't use the abc.Formula property to avoid having to write three different formulas - one for each possible column change).
I'm in the process of writing/editing a macro, but after I run the macro once, the Expressions in the Locals Watch window disappear, and if I try to run it again it produces an "Excel has encountered an error and needs to close". The macro probably doesn't make much sense out-of-context, but perhaps there's a glaring error that's causing this. A guess - possibly related to the Error Handling in the vba? (1st time I've used this in a macro).
Option Explicit
Sub Compare()
Dim strNaspK As String
Dim strCtryK As String
Dim intCtryRev As Integer
Do Until ActiveCell. Offset(1, 0) = ""
ActiveCell.Offset(1, 0).Select
strNaspK = ActiveCell
strCtryK = ActiveCell.Offset(0, 2)
On Error Goto ErrJump:........................
Basically, I've got two sets of data on different sheets, with NASP ID and country being variables. Just trying to import the data from the 'Comparison' sheet, if both the NASP id and country matches that on the first sheet, established by the variables.
The data to be filtered is in several sheets, and once filtered is to be copied to a destination sheet (in this case "Temp"). The criteria for advanced filter is on an altogether different sheet (in this case "Reports"). The macro is actually simplified for the purpose of the question, and I want to re-use the code several times, hence the use of variable "filterRng". When I run it, I get the subject error at the bolded line in the code below. I'm thinking that the Advanced Filter doesn't like a variable as a range reference, as it runs perfectly well if the commented out line below the problem line is used instead.
Sub Test()
Dim i As Integer
Dim rngData As Range
Dim filterRng As Range
Set filterRng = Sheets("Reports").Range("A121:K124")
Application. ScreenUpdating = False
Application.DisplayAlerts = False
I'm trying to put a search userform in a worksheet. I got this code from the web and tried to alter it to fit my worksheet....
View 7 Replies View RelatedExcel 2007. Basically, I'll be working in excel and all of a sudden, my screen sort of blurs; gets scramble like below. This only happens when using Excel.
Myscreen.PNG