Do filtering with a finite range with VBA

Question

Do filtering with a finite range with VBA

Navigation

#1 by (2 votes)

0

I want to do a self-filtering through VBA of a table with multiple columns.

My intention is that it does not filter to infinity, if I do not always have control knowing which is the last record of my table.

My code is the following:

last = Range(sh.Cells(2, 1), sh.Cells(2, 1)).End(xlDown).Row

With sh ' Viene de un set. Se viene a referir a la hoja actual 
    .AutoFilterMode = False
    .Range(.Cells(2, 20), .Cells(last, 20)).AutoFilter 20, "No" 'EL ERROR ME LO DA CON RANGE
End With

The error shown by the excel is the following: "Error in the AutoFilter method of the Range class."

Any ideas?

Thank you very much.

vba excel

asked by David_helo 08.08.2018 в 11:55

source

1 answer

Clear screen on Mac. Language C [closed] what would be the correct way to solve this exercise in php ?, I'm doing it like that but the syntax is wrong

score 2 · Accepted Answer

What happens to you is that you are trying to filter by field # 20 with the keyword "No" but you are not indicating it in the code. Try putting Field and Criteria1:

last = Range(sh.Cells(2, 1), sh.Cells(2, 1)).End(xlDown).Row

With sh ' Viene de un set. Se viene a referir a la hoja actual 
    .AutoFilterMode = False
    .Range(.Cells(1, 20), .Cells(last, 20)).AutoFilter Field:=1, Criteria1:="No" 
'IMPORTANTE: PONER Cells (1,20) si queremos que empiece por la fila 2, de lo contrario se saltará esta. 



End With