Macro para celda de excel

Le hice unos pequeños cambios a tu archivo en el código para hacerlo, desde mi punto de vista, un poco más eficiente, te estoy enviando el archivo en este momento con las modificaciones, te cuento las principales y en todo caso revisa el código y me cuentas que opinas o si hay que hacerle algún arreglo:
Primero: Modifique las macros de los filtros avanzados para que adicional a hacer el filtro modifiquen unos nombres de rangos en la hoja, los nombres son:
Nombres
Cargos
Segundo: Las macros antes de hacer el autofiltro seleccionan el rango que contenga datos y lo aplican sobre la totalidad de los datos, es decir que si agregas nombres nuevos no debes modificar las macros (hay que revisar las fórmulas de la Hoja1) pues queda automático todo
Tercero: Las validaciones de las celdas del problema en la Hoja1 quedaron referenciadas a los rangos antes descritos para evitar que salgan ceros debajo de los nombres posibles
Cuarto: Al modificar la celda de los nombres si hay más de un cargo posible te despliega la lista de opciones para que el usuario seleccione.
Bueno, espero que te sirva para lo que necesitas, si no tienes preguntas adicionales te pido el favor de finalizar la pregunta con una valoración acorde con la calidad y oportunidad de la respuesta.

Excelente respuesta, mil gracias Cesar
Te estaré molestando
Arume

Bueno lo de pruebas fue que olvidé borrarla... lo puedes hacer, eso lo hago cuando desconozco algunos comandos conla grabadora de macros para darme una guía, ahí lo que estaba buscando era como nombrar rangos en el libro a través de código
La primera pregunta corresponde a una evaluación de si hay más de un Cargo para ese nombre pues si lo hago sin la validación (me ocurrió) me marca como rango "Cargos" el cargo y todas las celdas en blanco que encuentre hacia abajo.
Espero haber resuelto tus dudas y me alegra que te haya parecido bien la solución.

Hola Cesar! Excelente tu respuesta. Fue de excelente utilidad, pero como no me gusta quedar con dudas me gustaría me dijeras que realiza el siguiente escrito de la macro filtrar cargos:
If Range("K7").Value <> "" Then Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Names.Add Name:="Cargos", _
RefersToR1C1:="=Hoja2!" & Selection.Address(ReferenceStyle:=xlR1C1)
y tambien los siguientes renglones de la macro pruebas:
ActiveWorkbook.Names.Add Name:="Nombres", RefersToR1C1:="=Hoja2!R4C9:R10C9"
Gracias
Te finalizo la pregunta apenas me respondas porque entiendo que si la finalizo ahora no me puedes contestar
Arume