Lista desplegable
Que pasa fejoal, como estas!
Oye, tengo una tabla hecha en excel la cual quiero que me sirva para mostrar sus datos en listas desplegables (validación) en un formulario que estoy haciendo, lo que no me gusta es que yo nombre las columnas, ejemplo: de la A1:A65536 le puse Rubros, de la B1:B65536 Tipos, etc. Pero no todas las columnas tienen la misma cantidad de información, ejemplo: rango Rubros tiene 5 registros, rango Tipos tiene 40 registros; entonces si yo hago la validación en una celda, le pongo que va a ser una lista y en origen le pongo =Rubros me toma el tamaño de la lista más grande, en este caso de 40 registros, así que muchas salen con bastantes espacios en blanco.
La pregunta es: ¿Cómo hacerle para que no muestre esos espacios en blanco?
Gracias...
Oye, tengo una tabla hecha en excel la cual quiero que me sirva para mostrar sus datos en listas desplegables (validación) en un formulario que estoy haciendo, lo que no me gusta es que yo nombre las columnas, ejemplo: de la A1:A65536 le puse Rubros, de la B1:B65536 Tipos, etc. Pero no todas las columnas tienen la misma cantidad de información, ejemplo: rango Rubros tiene 5 registros, rango Tipos tiene 40 registros; entonces si yo hago la validación en una celda, le pongo que va a ser una lista y en origen le pongo =Rubros me toma el tamaño de la lista más grande, en este caso de 40 registros, así que muchas salen con bastantes espacios en blanco.
La pregunta es: ¿Cómo hacerle para que no muestre esos espacios en blanco?
Gracias...
3 Respuestas
Respuesta de fejoal
1
Digamos primero que definir un rango tan grande es un despropósito, toda vez que difícilmente vayas a ocupar absolutamente toda la columna. De hecho, estarás forzando a MS Excel a que lea todas y cada unas de sus celdas cada vez que tales nombres estén involucrados, lo cual, claramente, demorará -en alguna medida- los procesos.
De todos modos, definirlos de menor tamaño, tampoco resolverá el problema que planteas.
Para este caso, lo que yo suelo hacer, es definir rangos dinámicos. Esto es, rangos que se auto-definen en función de los elementos que contengan.
Por ejemplo, en tu caso, suponiendo que los datos que van en rango llamado "Rubros" estuviera en la columna B de la hoja Tablas,
Al definirlo deberías ingresa en la casilla inferior (donde va la dirección del rango), de la siguiente manera:
=DESREF('Tablas'!$B$2;;;CONTARA('Tablas'!$B$2:$B$500))
Una vez definido, de esta manera, el rango "Rubros" comenzará en la celda B2 y tendrá tantas filas de alto como elementos cuente en el rango que lo contiene. Cuida que no haya lineas en blanco entre los rubros.
Luego, en la Lista de Validación al usar este nombre de rango, ceñido a los elementos que contenga, no deberás ver lineas en blanco.
Por otra parte, si agregas elementos a aquella tabla, no necesitas redefinir el rango, pues lo hará la fórmula conque lo definiste.
Otro tanto haz, con los otros rangos a usar en las listas de validación.
Espero haber ayudado a resolver tu problema.
Un abrazo!
Fernando
De todos modos, definirlos de menor tamaño, tampoco resolverá el problema que planteas.
Para este caso, lo que yo suelo hacer, es definir rangos dinámicos. Esto es, rangos que se auto-definen en función de los elementos que contengan.
Por ejemplo, en tu caso, suponiendo que los datos que van en rango llamado "Rubros" estuviera en la columna B de la hoja Tablas,
Al definirlo deberías ingresa en la casilla inferior (donde va la dirección del rango), de la siguiente manera:
=DESREF('Tablas'!$B$2;;;CONTARA('Tablas'!$B$2:$B$500))
Una vez definido, de esta manera, el rango "Rubros" comenzará en la celda B2 y tendrá tantas filas de alto como elementos cuente en el rango que lo contiene. Cuida que no haya lineas en blanco entre los rubros.
Luego, en la Lista de Validación al usar este nombre de rango, ceñido a los elementos que contenga, no deberás ver lineas en blanco.
Por otra parte, si agregas elementos a aquella tabla, no necesitas redefinir el rango, pues lo hará la fórmula conque lo definiste.
Otro tanto haz, con los otros rangos a usar en las listas de validación.
Espero haber ayudado a resolver tu problema.
Un abrazo!
Fernando
Fantástico!, ya había usado esta fórmula del DESREF para que me devolviera un rango de celdas pero no estoy muy familiarizado con la introducción de fórmulas en el cuadro origen de la validación, muchas gracias...
Respuesta de perdita
1
Para mí siempre es buena opción. Lo que debes hacer es pasarle la instrucción CurrentRegion. De esta forma te dimensiona el rango a las filas que realmente tengan datos.
Prueba por ejemplo algo así:
Dim Rubros As Range
Dim Tipos As Range
Set Rubros = Range("Rubros").CurrentRegion
Set Tipos = Range("Tipos").CurrentRegion
Tiene el problema de los espacios en blanco, si en el rango tienes celdas en blancos no te sirve el CurrentRegion, por lo que deduzco de tu consulta no tienes celdas en blanco.
Con estas instucciones le pasarías tu el rango de trabajo.
Dim celdas As String
celdas = InputBox("Introduce rango en formato A1:N1 ")
Range(celdas).Name = "Rubros"
Mira si es la solución que buscabas, cualquier cosa no dudes en comentar de nuevo. Si te ha servido mi respuesta, ruego cieres la pregunta.
Prueba por ejemplo algo así:
Dim Rubros As Range
Dim Tipos As Range
Set Rubros = Range("Rubros").CurrentRegion
Set Tipos = Range("Tipos").CurrentRegion
Tiene el problema de los espacios en blanco, si en el rango tienes celdas en blancos no te sirve el CurrentRegion, por lo que deduzco de tu consulta no tienes celdas en blanco.
Con estas instucciones le pasarías tu el rango de trabajo.
Dim celdas As String
celdas = InputBox("Introduce rango en formato A1:N1 ")
Range(celdas).Name = "Rubros"
Mira si es la solución que buscabas, cualquier cosa no dudes en comentar de nuevo. Si te ha servido mi respuesta, ruego cieres la pregunta.
Respuesta de valedor
1
No entendí lo siguiente...
Dices...
- Mostrar sus datos en listas desplegables (validación) en un formulario -
Yo entiendo por formulario, un UserForm que agregas desde el Editor VBA, pero más adelante dices...
- Si yo hago la validación en una celda, le pongo que va a ser una lista y en origen le pongo =Rubros -
Así que considerare que lo que quieres es que la lista de validación sea dinámica, es decir, se adapte al máximo numero de datos que tenga la lista, para hacer esto necesitas usar un evento y algunas lineas de código, no se que experiencia tengas en esto, pero si sigues los siguientes paso debe de resultar...
Entramos a Excel
Abrimos el libro de trabajo
Entramos al Editor VBA (Alt+F11)
Damos un doble click en la hoja donde esta la celda donde quedara la lista de validación, esto lo hacemos desde el Explorador de Proyectos, uno muy parecido al Explorador de Windows, si no lo ves, ve al menu Ver | Explorador de proyectos...
En la ventana de código (la más grande que se ve), copia el siguiente código...
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim strRango As String
If Target.Cells.Count = 1 Then
If Target.Address(False, False) = "C1" Then
strRango = "=$A$1:$A$" & Format(Range("A1").End(xlDown).Row)
With Target.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:=strRango
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End If
End If
End Sub
Observa la linea...
If Target.Address(False, False) = "C1" Then
Aquí le decimos que en C1 es donde actualizaremos la lista de validación, tienes que poner la celda correcta que necesites, también, es código se puede modificar para que valides más de una celda, por ejemplo, el siguiente código esta modificado para que puedas validar las celdas C1 y D1, cuando las selecciones, la lista de validación se adaptara al contenido de la listas de las columnas A y B, respectivamente, saludos...
Mauricio Baeza
[email protected]
Todo lo que no es dado es perdido
*==========================================*
La presente respuesta ha demandado un tiempo de lectura,
análisis, elaboración y redacción. A cambio, simplemente,
pretendo que te tomes un minuto para FINALIZARLA, si
hubiera satisfecho -en alguna medida- tu requerimiento.
En caso contrario, puedes preguntarme nuevamente
aclarando qué entendí mal o qué faltó.
Dices...
- Mostrar sus datos en listas desplegables (validación) en un formulario -
Yo entiendo por formulario, un UserForm que agregas desde el Editor VBA, pero más adelante dices...
- Si yo hago la validación en una celda, le pongo que va a ser una lista y en origen le pongo =Rubros -
Así que considerare que lo que quieres es que la lista de validación sea dinámica, es decir, se adapte al máximo numero de datos que tenga la lista, para hacer esto necesitas usar un evento y algunas lineas de código, no se que experiencia tengas en esto, pero si sigues los siguientes paso debe de resultar...
Entramos a Excel
Abrimos el libro de trabajo
Entramos al Editor VBA (Alt+F11)
Damos un doble click en la hoja donde esta la celda donde quedara la lista de validación, esto lo hacemos desde el Explorador de Proyectos, uno muy parecido al Explorador de Windows, si no lo ves, ve al menu Ver | Explorador de proyectos...
En la ventana de código (la más grande que se ve), copia el siguiente código...
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim strRango As String
If Target.Cells.Count = 1 Then
If Target.Address(False, False) = "C1" Then
strRango = "=$A$1:$A$" & Format(Range("A1").End(xlDown).Row)
With Target.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:=strRango
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End If
End If
End Sub
Observa la linea...
If Target.Address(False, False) = "C1" Then
Aquí le decimos que en C1 es donde actualizaremos la lista de validación, tienes que poner la celda correcta que necesites, también, es código se puede modificar para que valides más de una celda, por ejemplo, el siguiente código esta modificado para que puedas validar las celdas C1 y D1, cuando las selecciones, la lista de validación se adaptara al contenido de la listas de las columnas A y B, respectivamente, saludos...
Mauricio Baeza
[email protected]
Todo lo que no es dado es perdido
*==========================================*
La presente respuesta ha demandado un tiempo de lectura,
análisis, elaboración y redacción. A cambio, simplemente,
pretendo que te tomes un minuto para FINALIZARLA, si
hubiera satisfecho -en alguna medida- tu requerimiento.
En caso contrario, puedes preguntarme nuevamente
aclarando qué entendí mal o qué faltó.