[求教] excel vba filber, 3項 不包括

LEARZA

ActiveSheet.Range("A:Z").AutoFilter Field:=1, Criteria1:=Array( _
        "<>A", "<>B", "<>C"), Operator:=xlFilterValues

這錯誤的不能運行
請指導細佬應該點搞

狂蜂一號

Try macro recorder?

javacomhk

1. ActiveSheet.Range("A:Z").AutoFilter Field:=1, Criteria1:="<>A", Operator:=xlAnd, Criteria2:="<>B", Operator:=xlAnd, Criteria3:="<>C"

複製代碼

LEARZA

EXCEL 2021 USING

javacomhk 發表於 2023-3-24 20:48

---------------------------
Microsoft Visual Basic for Applications
---------------------------
Run-time error '1004':

Application-defined or object-defined error
---------------------------
OK   Help   
---------------------------

platinum

The Excel VBA code you provided is attempting to filter a range of cells on the active worksheet based on certain criteria.

The reason why this VBA code may not be running could be due to a syntax error. Here is a possible corrected version of the code:

ActiveSheet.Range("A:Z").AutoFilter Field:=1, Criteria1:="<>A", Criteria2:="<>B", Criteria3:="<>C", Operator:=xlFilterValues
In this corrected version of the code, we use the Criteria1, Criteria2, and Criteria3 parameters to specify multiple criteria for the filter. We also use the <> operator to exclude cells that contain the values "A", "B", or "C".

Note that the number of criteria that can be specified in this way is limited to three. If you need to specify more criteria, you may need to use a different approach, such as creating an array of values and looping through them to apply the filter.

It's also possible that the code is failing for another reason, such as the range of cells being filtered not being on the active worksheet or the worksheet being protected. In these cases, you may need to adjust the code accordingly or take additional steps to ensure that the code can run successfully.




Answer by Chat_ _ _

platinum

The "Run-time error '1004': Application-defined or object-defined error" message is a common error that occurs in Excel VBA when there is an issue with the code referencing a range or object that does not exist, is incorrectly specified, or is protected.

In the case of the VBA code you provided, there are a few reasons why this error may occur. Here are some things you can check:

Make sure the active worksheet has data in column A through Z. If the range being filtered does not exist or is empty, you will get this error.

Check the syntax of the code to ensure that it is correct. Make sure that there are no typos or missing characters in the code.

Ensure that the worksheet is not protected. If the worksheet is protected, you may get this error if the code is trying to modify a protected cell or range.

Try adding a reference to the worksheet in the code to ensure that the correct worksheet is being filtered. For example, you can use the following code to reference a specific worksheet:

Worksheets("Sheet1").Range("A:Z").AutoFilter Field:=1, Criteria1:="<>A", Criteria2:="<>B", Criteria3:="<>C", Operator:=xlFilterValues
If none of the above solutions work, try stepping through the code using the VBA debugger to identify the specific line of code that is causing the error.
By checking these potential issues, you may be able to resolve the "Run-time error '1004': Application-defined or object-defined error" message and get your VBA code to run successfully.



More from Chat _ _ _

javacomhk

您提到的錯誤確實可能發生在您嘗試選擇未激活工作表上的範圍時。您可能需要先激活工作表，然後再選擇範圍。例如，您可以在選擇 Sheet1 上的範圍之前使用 Sheets(“Sheet1”).Activate 來激活 Sheet1。

LEARZA

FILTER 能有 Criteria3 嗎?

ELON

FILTER 能有 Criteria3 嗎?
LEARZA 發表於 2023-3-27 00:47

可以

via HKEPC IR Pro 3.6.1 - iOS(3.0.0)