Cambiar rango fijo ("A1:M17") por uno variable
Grabe una macro para ordenar datos de A1 a M1Cuando grabe la macro tenia datos en este rango, pero deseo que vaya cambiando de acuerdo a como vaya metiendo datos que inicie en ("A1:M1") y según vaya metiendo datos el rango se amplíe para ordenar los datos. Te paso el código para que comprendas
Sub Ordenar_responsable2()
'
' Ordenar_reponsable2 Macro
Columns("I:I").Select
ActiveWorkbook.Worksheets("BASEGENE").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("BASEGENE").Sort.SortFields.Add Key:=Range("I1").End(xlDown), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("BASEGENE").Sort
.SetRange Range("A1:M17")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1").Select
End Sub
Gracias por tu atención
Sub Ordenar_responsable2()
'
' Ordenar_reponsable2 Macro
Columns("I:I").Select
ActiveWorkbook.Worksheets("BASEGENE").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("BASEGENE").Sort.SortFields.Add Key:=Range("I1").End(xlDown), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("BASEGENE").Sort
.SetRange Range("A1:M17")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1").Select
End Sub
Gracias por tu atención
1 Respuesta
Respuesta de Juan Carlos González Chavarría
1
a), yo en tu lugar usaría un rango dinámico. Si no sabes lo que es, entonces estamos un poco mal. Existen varias técnicas para crearlos según lo que se necesite y según el tamaño de la Base o Tabla que estemos utilizando. Voy a tratar de explicarte una técnica.
Si constantemente necesita actualizar y añadir información a los datos, o si
trabaja con gráficos y tablas dinámicas, deseará poder crear rangos dinámicos
con nombre, que se expandan y contraigan en función de los datos.
Para entender cómo funcionan los rangos con nombre, debería, en primer
lugar, familiarizarse con la función DESREF (si es que no lo ha hecho todavía),
una de las funciones de búsqueda de referencia de Excel.
Comenzaremos con el rango dinámico con nombre más simple, aquel que se
expandirá hacia abajo una única columna, pero solamente tanto como entradas
contenga la columna. Por ejemplo, se la columna A contiene 1O filas continuas
de datos, el rango dinámico con nombre incorporará el rango A1 :A1O. Siga estos
pasos para crear dicho rango.
Vaya a InsertapNombre>Definir, introduzca "MiRango" en el cuadro de texto
Nombres en el libro y en el cuadro de texto Se refiere a, introduzca lo siguiente:
=DESREF( $A$1 ; 0 ; 0 ; CONTARA($ A$1 : $A$100 ) ; 1)
Ahora haga clic en Agregar y luego en Aceptar.
Cuando defina el rango para CONTARA, evite la tentación de incluir
toda la columna de datos, para así no obligar a dicha función a contar
miles de celdas innecesarias.
Teniendo ahora los datos en la columna A, este rango con nombre incorporará
todos los datos que se encuentren en filas contiguas, comenzando por la celda
A1 . Si desea comprobar el rango dinámico con nombre, puede hacerlo de diferentes
formas.
Por desgracia, los rangos dinámicos con nombre no están accesibles a través
del Cuadro de nombres estándar de la parte superior izquierda de la ventana de Excel. Pero sí puede hacer clic en dicho cuadro, escribir el nombre "MiRango" y
pulsar Intro. Excel seleccionará automáticamente el rango. Por supuesto, también
puede utilizar el cuadro de diálogo lr a yendo a Edición>lr a (Control/Comando-
I), escribiendo "MiRango" en el cuadro de texto y haciendo clic enAceptar.
El rango dinámico con nombre que acaba de crear en el ejemplo anterior contiene
la función CONTARA como parámetro de la función DESREF.
Recuerde que la función CONTARA contará todas las celdas que no
estén vacías. Tenga en cuenta que esto también incluye las fórmulas
que dichas celdas contenga, que puedan estar devolviendo un texto
vacío ("").
Si tiene una lista que sólo contiene datos numéricos y al final de ella desea
almacenar un texto, pero no quiere que dicho texto quede incluido como parte
del rango dinámico con nombre, puede reemplazar la función CONTARA con la
función estándar de Excel CONTAR, función que sólo cuenta las celdas que contienen
datos numéricos.
El siguiente ejemplo, utilizaremos el rango dinámico con nombre para definir
una tabla de datos que deseamos sea dinámica. Para ello, escriba la siguiente
función en el cuadro de texto Se refiere a:
:DFqpFF I <¡ ( 1 . n . n . CONTARA ( gAg 1 : gAg 1 0 0 ) ; CONTARA ( $ 1 : g1 ) )
Aquí, el rango dinámico con nombre se expandirá hacia abajo tantas entradas
como datos haya que la columna A, y a lo largo de tantas filas como encabezados
haya en la fila 1. Si está seguro de que el número de columnas para la tabla se va
a mantener estable, puede reemplazar la segunda función CONTARA por un número
fijo, como pueda ser 10.
El único problema al utilizar rangos dinámicos con nombre para una tabla de
datos, es que presupone que la columna A establecerá la longitud máxima para
la tabla. En la mayoría de los casos, esto será cierto, pero a veces, puede que la
columna más larga sea otra diferente. Para evitar este problema, puede utilizar
la función MAX de Excel, que devuelve el número más alto en un rango de celdas.
Utilice la fila 1 para almacenar un número de las funciones CONTARA que
están haciendo referencia a la columna y, por tanto, devolüendo el número de
entradas en cada columna. Utilice la función MAX para el argumento "alto" de la
función DESREF. Esto asegura que el rango dinámico con nombre para la tabla
siempre se expandirá tanto como la columna más larga. Por supuesto, puede
ocultar la fila 1, ya que el usuario no necesita verla.
Ahora ya puedes utilizar tu código de la siguiente forma:
Combobox.rowsource:= Range("MyRango")
Si aún no entiendes, entonces envíame tu archivo y trato de ayudarte directamente.
Si constantemente necesita actualizar y añadir información a los datos, o si
trabaja con gráficos y tablas dinámicas, deseará poder crear rangos dinámicos
con nombre, que se expandan y contraigan en función de los datos.
Para entender cómo funcionan los rangos con nombre, debería, en primer
lugar, familiarizarse con la función DESREF (si es que no lo ha hecho todavía),
una de las funciones de búsqueda de referencia de Excel.
Comenzaremos con el rango dinámico con nombre más simple, aquel que se
expandirá hacia abajo una única columna, pero solamente tanto como entradas
contenga la columna. Por ejemplo, se la columna A contiene 1O filas continuas
de datos, el rango dinámico con nombre incorporará el rango A1 :A1O. Siga estos
pasos para crear dicho rango.
Vaya a InsertapNombre>Definir, introduzca "MiRango" en el cuadro de texto
Nombres en el libro y en el cuadro de texto Se refiere a, introduzca lo siguiente:
=DESREF( $A$1 ; 0 ; 0 ; CONTARA($ A$1 : $A$100 ) ; 1)
Ahora haga clic en Agregar y luego en Aceptar.
Cuando defina el rango para CONTARA, evite la tentación de incluir
toda la columna de datos, para así no obligar a dicha función a contar
miles de celdas innecesarias.
Teniendo ahora los datos en la columna A, este rango con nombre incorporará
todos los datos que se encuentren en filas contiguas, comenzando por la celda
A1 . Si desea comprobar el rango dinámico con nombre, puede hacerlo de diferentes
formas.
Por desgracia, los rangos dinámicos con nombre no están accesibles a través
del Cuadro de nombres estándar de la parte superior izquierda de la ventana de Excel. Pero sí puede hacer clic en dicho cuadro, escribir el nombre "MiRango" y
pulsar Intro. Excel seleccionará automáticamente el rango. Por supuesto, también
puede utilizar el cuadro de diálogo lr a yendo a Edición>lr a (Control/Comando-
I), escribiendo "MiRango" en el cuadro de texto y haciendo clic enAceptar.
El rango dinámico con nombre que acaba de crear en el ejemplo anterior contiene
la función CONTARA como parámetro de la función DESREF.
Recuerde que la función CONTARA contará todas las celdas que no
estén vacías. Tenga en cuenta que esto también incluye las fórmulas
que dichas celdas contenga, que puedan estar devolviendo un texto
vacío ("").
Si tiene una lista que sólo contiene datos numéricos y al final de ella desea
almacenar un texto, pero no quiere que dicho texto quede incluido como parte
del rango dinámico con nombre, puede reemplazar la función CONTARA con la
función estándar de Excel CONTAR, función que sólo cuenta las celdas que contienen
datos numéricos.
El siguiente ejemplo, utilizaremos el rango dinámico con nombre para definir
una tabla de datos que deseamos sea dinámica. Para ello, escriba la siguiente
función en el cuadro de texto Se refiere a:
:DFqpFF I <¡ ( 1 . n . n . CONTARA ( gAg 1 : gAg 1 0 0 ) ; CONTARA ( $ 1 : g1 ) )
Aquí, el rango dinámico con nombre se expandirá hacia abajo tantas entradas
como datos haya que la columna A, y a lo largo de tantas filas como encabezados
haya en la fila 1. Si está seguro de que el número de columnas para la tabla se va
a mantener estable, puede reemplazar la segunda función CONTARA por un número
fijo, como pueda ser 10.
El único problema al utilizar rangos dinámicos con nombre para una tabla de
datos, es que presupone que la columna A establecerá la longitud máxima para
la tabla. En la mayoría de los casos, esto será cierto, pero a veces, puede que la
columna más larga sea otra diferente. Para evitar este problema, puede utilizar
la función MAX de Excel, que devuelve el número más alto en un rango de celdas.
Utilice la fila 1 para almacenar un número de las funciones CONTARA que
están haciendo referencia a la columna y, por tanto, devolüendo el número de
entradas en cada columna. Utilice la función MAX para el argumento "alto" de la
función DESREF. Esto asegura que el rango dinámico con nombre para la tabla
siempre se expandirá tanto como la columna más larga. Por supuesto, puede
ocultar la fila 1, ya que el usuario no necesita verla.
Ahora ya puedes utilizar tu código de la siguiente forma:
Combobox.rowsource:= Range("MyRango")
Si aún no entiendes, entonces envíame tu archivo y trato de ayudarte directamente.
