filtros avanzados

hola, eduardo !

queria saber si es posible que al grabar en una macro un filtro avanzado de registros unicos
puedo cambiar el campo en el que filtrar sin tener que modificar la macro
y sin tener que meter yo el codigo manualmente, ya que no se programar.
es decir, si tengo una base de datos de ventas de articulos hechas por distintos vendedores a distintos clientes
querria que el mismo filtro avanzado en el que traigo por ejemplo los articulos
me sirva tambien para poder traerme los vendedores o los clientes...

sin "ver" el codigo que genero la grabadora de macros (va a estar menos facil, pero)...
asumiendo que ya entendiste como funcionan los criterios para los filtros avanzados...
(y/o revisa la informacion correspondiente en la ayuda en linea del excel -> {F1})

solo necesitas tener un rango para los criterios avanzados (minimo con dos filas en donde)...
- la primer fila incluye los titulos de los campos donde se aplicarian los criterios (pueden ser todos o solo algunos)
- en la segunda fila pondras cuales son los criterios (segun el campo/columna) para la extraccion
- para el campo/columna (fila 1) que no requieras algun criterio especifico (simplemente) dejas en blanco su celda (fila 2)

el rango de criterios que definas es lo que pones en la macro para el argumento: CriteriaRange:=Range("m1:q2") (es ejemplo)
si necesitas criterios del tipo "O" (alguna de varias condiciones para un campo/columna) agregas mas filas al rango de criterios

si cualquier duda (o informacion adicional)... comentas ?
saludos,
hector.

queria saber si es posible que al grabar en una macro un filtro avanzado de
registros unicos puedo cambiar el campo en el que filtrar sin tener que
modificar la macro y sin tener que meter yo el codigo manualmente, ya que no
se programar.

hola Eduardo [y Hector]

estaba preparando un ej. para el hilo cuando he visto el mensaje de Hector, y aunque seguramente sea un poco enrevesado,
[sobre todo si lo comparamos con tu capacidad para el KISS, Hector], ya que estaá lo pongo para que le echeis un ojo si
quereis

ahora que Hector te ha esplicado el funcionamiento de los filtros, si quieres prueba el ej. que te pongo al final.

En el estan reflejados parte de lo que, gracias precisamente a Hector Miguel (al menos en su <inmensa> mayoria) he
aprendido por aqui sobre los filtros avanzados. (Aunque estoy seguro que no se parecen en nada (al menos en calidad y
sintesis) a los que èl podria ofrecer en el mismo caso). [De hecho hasta copio algunos terminos usados en algunas de sus
respuestas]

Requisitos del ejemplo (pruebalo en una copia ):

1) debes tener una hoja llamada 'Oculta' (que puedes ocultar si quieres). Aunque esta hoja se puede evitar, tambien te
da mas juego en cuanto a numeros de registros se refiere, y como te digo si quieres la ocultas

2) en la hoja que contiene la lista que quieres filtrar:inserta dos combobox de la barra de herramientas 'Cuadro de
controles' (si los pegas a la parte de arriba de la hoja, sobre la fila 1 , uno al lado de otro evitaras que se
desplacen con los filtrados)

NOTA: he supuesto que tu lista tiene menos de la mitad de las columnas posibles en excel (256), o sea un maximo de 127
columnas (ni tampoco excede esta columna)

3) copia los codigos que te pongo al final en el modulo de esta hoja (la de la lista).

Para abrir dicho modulo si quieres ve a la pestaña de la hoja => boton secundario => Ver codigo. En la 'hoja' en blanco
que se te habra abierto pega lo que te indico.

OJO: si te fijas en el codigo, en la parte de arriba (area de declaraciones), tras los comentarios iniciales se declaran
dos constantes que corresponden a la 1ª fila y columna de la lista (titulos incluidos). Si la lista no empieza en A1
cambia los valores asignados (1 para ambos en este caso, por los que correspondan),
Estan puestas sobre todo para facilitarte la adptacion, pero si tienes claros los rangos reales, podrias sustituirlas,
aligerando ya de paso bastante el codigo.

antes de cada codigo hay una breve explicacion de los mismos y de las variables que intervienen

4) vuelve a excel. Si tienes activado el boton de 'Modo diseño' (escuadrita) desactivalo. Sal un momento de esa hoja
yendo a otra y vuelve a ella (Para cargar el combo 1)

ahora puedes probar a seleccionar en los combos a ver que tal


' pega desde aqui --

' las constantes 'pF' y 'pC' se corresponden con el
' nº de la 1ª fila y 1ª columna respectivamente
' de la lista (fila titulos incluida). Cambia el 1
' por lo que corresponda si no empiezan en A1
Const pF As Byte = 1, pC As Byte = 1
'
' esta variable evitara que se dispare el evento
' change del combo 2 cuando lo cargamos desde el 1
' Es posible que en este caso no haga falta, pero ...
Dim sinEvento As Boolean
'
' desde este combo filtramos la lista (y cargamos el
' combo 2)con los registros unicos del campo seleccionado
' la variable 'Col' tomara el valor de la columna
' correspondiente al campo seleccionado en el
' combobox1
' la variable 'ultF' se corresponde con la ultima fila
' de la lista y aqui se podria omitir poniendo su
' valor directamente, pues solo se usa 1 vez, pero
' es para que quedara un poco mas claro
'
Private Sub ComboBox1_Change()
Dim ultF As Long, Col As Byte
Application.ScreenUpdating = False
On Error GoTo salir
Col = pC + ComboBox1.ListIndex
sinEvento = True: ComboBox2.Clear
With Me
If .FilterMode Then .ShowAllData
ultF = pF + .Cells(pF, pC) _
.CurrentRegion.Rows.Count - 1
With .Range(.Cells(pF, Col), .Cells(ultF, Col))
.AdvancedFilter _
Action:=xlFilterInPlace, _
criteriarange:="", _
unique:=True
With .Offset(1).Resize(.Rows.Count - 1) _
.SpecialCells(xlCellTypeVisible)
If .Rows.Count < 1 Then Exit Sub
Worksheets("Oculta").Columns.Clear
.Copy Worksheets("Oculta").[a1]
End With
With Worksheets("Oculta")
ComboBox2.List = .Range("a1:a" & _
.[a65536].End(xlUp).Row).Value
.[a:a].Clear
End With
End With
End With
salir:
sinEvento = False
End Sub
'
' desde este combo filtramos la lista con los registros
' que coincidan con el seleccionado en el.
' 'cR' es el nº de columna si le restamos a la ultima (256)
' el nº de columnas de la lista. Es para pegar los titulos
' del rango de criterios en donde no molesten
' 'Col' es lo mismo que en combo 1
' 'nCol' es el nº de columnas de la lista
' 'dato' es el valor del combo 2, que aunque quizas se
' podria pasar directamente, a mi por lo menos me suele
' dar problemas cuando los datos son fechas (y a veces
' con nros). Aqui paso 1º 'dato' para convertirla a lo
' que creo que son datos validos para el filtro
'
Private Sub ComboBox2_Change()
Dim cR As Integer, Col As Byte, nCol As Byte, dato
On Error GoTo salir
If sinEvento Then Exit Sub
If IsDate(ComboBox2) Then
dato = CLng(CDate(ComboBox2.Value))
ElseIf IsNumeric(ComboBox2) Then
dato = CSng(ComboBox2)
Else
dato = ComboBox2.Text
End If
Col = pC + ComboBox1.ListIndex
With Me
If .FilterMode Then .ShowAllData
nCol = .Cells(pF, pC).CurrentRegion.Columns.Count
cR = 256 - (nCol - 1)
.Range(.Cells(1, cR - 1), .Cells(2, 256)).Clear
.Range(.Cells(pF, pC), _
.Cells(pF, nCol + (pC - 1))).Copy .Cells(1, cR)
.Cells(2, cR + ComboBox1.ListIndex).Value = dato
With .Range(.Cells(pF, pC), _
.Cells(65536, nCol + (pC - 1)).End(xlUp))
.AdvancedFilter Action:=xlFilterInPlace, _
criteriarange:=.Range(.Cells(1, cR), _
.Cells(2, 256)), _
unique:=True
End With
.Range(.Cells(1, cR - 1), .Cells(2, 256)).Clear
End With
salir:
End Sub
'
' al activarse la hoja se carga el combo 1 con los nombres de campo,
' pero si hay posibilidad de que estos se modifiquen durante
' la sesion, deberias prever alguna forma de actualizarlo
' (manualmente valdria con ir a otra hoja y volver a esta)
'
Private Sub Worksheet_Activate()
ComboBox1.Column = Range(Cells(pF, pC), _
Cells(pF, Cells(pF, pC).CurrentRegion.Columns.Count - _
(pF - 1))).Value
End Sub

'-- pega hasta aqui -

bueno, lo dicho, solo por que ya estaban casi acabadas, y por si te dan alguna idea (y abiertas a cualquier tipo de
'cirugia' y /o comentario/correccion)

un saludo
Ivan

hola de nuevo, hay un par de cosas en los codigos, que aunque no
parecen afectar al funcionamiento, o estan mal o no me gusta como
estan

1ª) en el combo 1, si no hay coincidencias en el filtro
( If .Rows.Count < 1 Then Exit Sub),cosa improbable si no se ha
escrito nada manualmente, se salia del procedimiento sin restaurar la
variable SinEventos a false. Aunque no afecta, pues se supone que el
combo 2 estara vacio y por tanto poco se puede hacer, creo que lo suyo
es devolverla a false antes de salir, pej. cambiando la ainstruccion
por algo asi:=>

If .Rows.Count < 1 Then GoTo salir

2º) en el combo 2 hay un lapsus en el 2º bloque with (por otro lado
innecesario) con los puntos de asignacion. Apate se me habia olvidado
desactivar la actualizacion de pantalla

ya depaso le he quitado los bloques 'with me', que resultan mas
comodos para escribir el codigo, pero si no me equivoco no hacen
falta

Este seria el codigo de los dos combos

.***************************************************
Private Sub ComboBox1_Change()
Dim ultF As Long, Col As Byte
Application.ScreenUpdating = False
On Error GoTo salir
Col = pC + ComboBox1.ListIndex
sinEvento = True: ComboBox2.Clear
If FilterMode Then ShowAllData
ultF = pF + Cells(pF, pC) _
.CurrentRegion.Rows.Count - 1
With Range(Cells(pF, Col), Cells(ultF, Col))
.AdvancedFilter Action:=xlFilterInPlace, _
criteriarange:="", unique:=True
With .Offset(1).Resize(.Rows.Count - 1) _
.SpecialCells(xlCellTypeVisible)
If .Rows.Count < 1 Then GoTo salir
Worksheets("Oculta").[a:a].Clear
.Copy Worksheets("Oculta").[a1]
End With
End With
With Worksheets("Oculta")
ComboBox2.List = .Range("a1:a" & _
.[a65536].End(xlUp).Row).Value
.[a:a].Clear
End With
salir:
sinEvento = False
End Sub
'--
Private Sub ComboBox2_Change()
Dim cR As Integer, Col As Byte, nCol As Byte, dato
On Error GoTo salir
If sinEvento Then Exit Sub
If IsDate(ComboBox2) Then
dato = CLng(CDate(ComboBox2.Value))
ElseIf IsNumeric(ComboBox2) Then
dato = CSng(ComboBox2)
Else
dato = ComboBox2.Text
End If
Col = pC + ComboBox1.ListIndex
Application.ScreenUpdating = False
If FilterMode Then ShowAllData
nCol = Cells(pF, pC).CurrentRegion.Columns.Count
cR = 256 - (nCol - 1)
Range(Cells(1, cR - 1), Cells(2, 256)).Clear
Range(Cells(pF, pC), _
Cells(pF, nCol + (pC - 1))).Copy Cells(1, cR)
Cells(2, cR + ComboBox1.ListIndex).Value = dato
Range(Cells(pF, pC), _
Cells(65536, nCol + (pC - 1)).End(xlUp)) _
.AdvancedFilter Action:=xlFilterInPlace, _
criteriarange:=Range(Cells(1, cR), _
Cells(2, 256)), unique:=True
Range(Cells(1, cR - 1), Cells(2, 256)).Clear
salir:
End Sub
'************************************************************

sobre todo por si pudieran dar problemas no vistos

un saludo
Ivan

true y entre tanto

hola hector,

perdona que no respondiera antes, es que he estado fuera y hasta hoy no lo
he podido probar.

la cuestion es que lo que necesito que en cierta medida el filtro me permita
cambiar el rango de la lista, no el criterio, ya que siempre voy a traer
todos los datos unicos del campo correspondiente por lo que puede quedarse
en blanco los criterios.

es decir, que necesito que al ejecutar la macro pueda decidir el campo o
rango de la lista en la que quiero aplicar el filtro. supongo que esto sin
meter codigo a mano sera imposible, no?

muchas gracias por tu ayuda y espero tus comentarios.

saludos.

dubicm.


"Héctor Miguel" escribió en el mensaje
news:

hola, eduardo !

queria saber si es posible que al grabar en una macro un filtro avanzado
de registros unicos
puedo cambiar el campo en el que filtrar sin tener que modificar la macro
y sin tener que meter yo el codigo manualmente, ya que no se programar.
es decir, si tengo una base de datos de ventas de articulos hechas por
distintos vendedores a distintos clientes
querria que el mismo filtro avanzado en el que traigo por ejemplo los
articulos
me sirva tambien para poder traerme los vendedores o los clientes...

sin "ver" el codigo que genero la grabadora de macros (va a estar menos
facil, pero)...
asumiendo que ya entendiste como funcionan los criterios para los filtros
avanzados...
(y/o revisa la informacion correspondiente en la ayuda en linea del
excel -> {F1})

solo necesitas tener un rango para los criterios avanzados (minimo con dos
filas en donde)...
- la primer fila incluye los titulos de los campos donde se aplicarian los
criterios (pueden ser todos o solo algunos)
- en la segunda fila pondras cuales son los criterios (segun el
campo/columna) para la extraccion
- para el campo/columna (fila 1) que no requieras algun criterio
especifico (simplemente) dejas en blanco su celda (fila 2)

el rango de criterios que definas es lo que pones en la macro para el
argumento: CriteriaRange:=Range("m1:q2") (es ejemplo)
si necesitas criterios del tipo "O" (alguna de varias condiciones para un
campo/columna) agregas mas filas al rango de criterios

si cualquier duda (o informacion adicional)... comentas ?
saludos,
hector.

hola, eduardo !

la cuestion es que lo que necesito que en cierta medida el filtro me permita cambiar el rango de la lista, no el criterio
ya que siempre voy a traer todos los datos unicos del campo correspondiente por lo que puede quedarse en blanco los criterios.
es decir, que necesito que al ejecutar la macro pueda decidir el campo o rango de la lista en la que quiero aplicar el filtro.
supongo que esto sin meter codigo a mano sera imposible, no?

1) lo que no puedes pedir (ni a excel ni tampoco a quienes estamos "de este lado del mensaje")... es:
que se adivine lo que estas pensando (es decir)...

2) de este lado del mensaje solo se puede "ver" lo que expones en la consulta (y "tan claro como lo expones")

3) se puede desarrollar codigos que interactuen con el usuario para ejecutar acciones segun respuestas
(pero es indispensable conocer los detalles del como, cuando, de donde, a donde, por que (si/no), etc.)

4) puedes exponer detalles (en terminos de excel) como, rangos, hojas, libros, celdas_con_los_criterios, tipo de datos, etc.
o puedes buscar algun sitio de hospedaje para "subir" algun archivo con tu ejemplo de datos (disponible a cualquiera en el grupo)

si cualquier duda (o informacion adicional)... comentas ?
saludos,
hector.

__ las consultas anteriores __

queria saber si es posible que al grabar en una macro un filtro avanzado de registros unicos
puedo cambiar el campo en el que filtrar sin tener que modificar la macroy sin tener que meter yo el codigo manualmente
ya que no se programar.
es decir, si tengo una base de datos de ventas de articulos hechas por distintos vendedores a distintos clientes
querria que el mismo filtro avanzado en el que traigo por ejemplo los articulos
me sirva tambien para poder traerme los vendedores o los clientes...

sin "ver" el codigo que genero la grabadora de macros (va a estar menos facil, pero)...
asumiendo que ya entendiste como funcionan los criterios para los filtros avanzados...
(y/o revisa la informacion correspondiente en la ayuda en linea del excel -> {F1})

solo necesitas tener un rango para los criterios avanzados (minimo con dos filas en donde)...
- la primer fila incluye los titulos de los campos donde se aplicarian los criterios (pueden ser todos o solo algunos)
- en la segunda fila pondras cuales son los criterios (segun el campo/columna) para la extraccion
- para el campo/columna (fila 1) que no requieras algun criterio especifico (simplemente) dejas en blanco su celda (fila 2)

el rango de criterios que definas es lo que pones en la macro para el argumento: CriteriaRange:=Range("m1:q2") (es ejemplo)
si necesitas criterios del tipo "O" (alguna de varias condiciones para un campo/columna) agregas mas filas al rango de criterios