Cómo copiar una tabla con un filtro a Excel. Pegar en filas visibles en Excel. Copiar solo celdas visibles

09.09.2021 SO móvil

Formato condicional (5)
Listas y rangos (5)
Macros (procedimientos VBA) (63)
Varios (39)
Errores y fallos de Excel (4)

Cómo pegar celdas copiadas solo en celdas visibles/filtradas

En general, creo que el significado del artículo ya se desprende del título. Lo ampliaré un poco.

No es ningún secreto que Excel te permite seleccionar solo filas visibles (por ejemplo, si algunos de ellos están ocultos o se aplica algún filtro).

Por lo tanto, si copia sólo las celdas visibles de esta manera, se copiarán como se esperaba. Pero cuando intentas pegar algo copiado en un rango filtrado (o que contiene filas ocultas), el resultado del pegado no será exactamente el que esperabas. Los datos se insertarán incluso en filas ocultas.

Copie un solo rango de celdas y péguelo solo en las visibles
Para insertar datos solo en celdas visibles, puede utilizar la siguiente macro:

Opción explícita Dim rCopyRange como rango "Con esta macro copiamos los datos Sub My_Copy() Si Selection.Count > 1 Luego establezca rCopyRange = Selection.SpecialCells(xlVisible) De lo contrario: Establezca rCopyRange = ActiveCell End If End Sub "Con esta macro insertamos datos comenzando desde la celda seleccionada Sub My_Paste() Si rCopyRange no es nada, salga de Sub si rCopyRange.Areas.Count > 1 y luego MsgBox "¡El rango pegado no debe contener más de una región!",vbCrítico, "Rango no válido": Salga de Sub Dim rCell como rango, li como largo, le como largo, lCount como largo, iCol como entero, iCalculation como entero Application.ScreenUpdating = False iCalculation = Application.Calculation: Application.Calculation = -4135 For iCol = 1 To rCopyRange .Columns.Count li = 0: lCount = 0: le = iCol - 1 para cada rCell en rCopyRange.Columns(iCol).Cells Do If ActiveCell.Offset(li, le).EntireColumn.Hidden = False y _ ActiveCell.Offset (li, le).EntireRow.Hidden = False Entonces rCell.Copy ActiveCell.Offset(li, le): lCount = lCount + 1 Fin si li = li + 1 Bucle mientras lCount >= rCell.Row - rCopyRange.Cells(1 ).Fila Siguiente rCell Siguiente iCol Aplicación.ScreenUpdating = Verdadero: Aplicación.Calculación = iCalculation End Sub

Opción Explícita Dim rCopyRange As Range "Utilice esta macro para copiar los datos Sub My_Copy() Si Selection.Count > 1 Entonces establezca rCopyRange = Selection.SpecialCells(xlVisible) De lo contrario: Establezca rCopyRange = ActiveCell End If End Sub "Utilice esta macro para pegar los datos a partir de las celdas seleccionadas Sub My_Paste() Si rCopyRange no es nada, entonces salga Sub Si rCopyRange.Areas.Count > 1 Entonces MsgBox "¡El rango pegado no debe contener más de un área!", vbCritical, "Rango no válido": Salir Sub Dim rCell como rango, li como largo, le como largo, lCount como largo, iCol como entero, iCalculation como entero Application.ScreenUpdating = False iCalculation = Application.Calculation: Application.Calculation = -4135 For iCol = 1 To rCopyRange.Columns .Count li = 0: lCount = 0: le = iCol - 1 para cada rCell en rCopyRange.Columns(iCol).Cells Do If ActiveCell.Offset(li, le).EntireColumn.Hidden = False y _ ActiveCell.Offset(li , le).EntireRow.Hidden = False Entonces rCell.Copy ActiveCell.Offset(li, le): lCount = lCount + 1 Fin si li = li + 1 Bucle mientras lCount >= rCell.Row - rCopyRange.Cells(1). Fila Siguiente rCell Siguiente iCol Application.ScreenUpdating = Verdadero: Application.Calculation = iCalculation End Sub

Para completar la imagen, es mejor asignar estas macros a teclas de acceso rápido (en los códigos siguientes, esto se hace automáticamente al abrir un libro con el código). Para hacer esto, solo necesita copiar los códigos a continuación en el módulo Este libro (Este libro de trabajo) :

Opción explícita "Cancelar la asignación de teclas de acceso rápido antes de cerrar el libro Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.OnKey "^q": Application.OnKey "^w" End Sub "Asignar teclas de acceso rápido al abrir el libro Private Sub Workbook_Open() Aplicación .OnKey "^q", "My_Copy": Aplicación.OnKey "^w", "My_Paste" End Sub

Ahora puede copiar el rango deseado presionando las teclas Control + q , e insértelo en el filtrado - Control + w .

Descargar ejemplo

(46,5 KiB, 9.622 descargas)

Copie solo las celdas visibles y pegue solo en las visibles
A petición de los visitantes del sitio, decidí perfeccionar este procedimiento. Ahora es posible copiar cualquier rango: con filas ocultas, columnas ocultas y pegar celdas copiadas también en cualquier rango: con filas ocultas, columnas ocultas. Funciona exactamente igual que el anterior: pulsando teclas Control + q copiar el rango deseado (con filas y columnas ocultas/filtradas o no ocultas) y pegar con un atajo de teclado Control + w . La inserción también se realiza en filas y columnas ocultas/filtradas o sin ocultas.
Si el rango copiado contiene fórmulas, para evitar el desplazamiento de referencia, puede copiar solo los valores de la celda, es decir, Al insertar valores, no se insertarán fórmulas, sino el resultado de su cálculo. O si es necesario conservar los formatos de las celdas en las que se produce la inserción, solo se copiarán y pegarán los valores de las celdas. Para hacer esto, necesita reemplazar la línea en el código (en el archivo a continuación):

rCell.Copiar rResCell.Offset(lr, lc)

a esto:

rResCell.Offset(lr, lc) = rCell.Value

Ambas líneas están presentes en el archivo a continuación; sólo necesita dejar la que sea más adecuada para sus tareas.

Descargar ejemplo:

(54,5 KiB, 7.928 descargas)

Ver también:
[]

¿Te ayudó el artículo? ¡Comparte el enlace con tus amigos! Videotutoriales

("Barra inferior":("textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24," textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance" :30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600 ,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500," textcss":"pantalla:bloque; texto-align:left;","textbgcss":"pantalla:absoluta; 0px; color de fondo:#333333; ","titlecss":"display:bloquear; posición: relativa; fuente: negrita 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; posición: relativa; fuente:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff; margin-top:8px;","buttoncss":"display:bloque; posición: relativa; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive": "","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40))

Si algunas celdas, filas o columnas de una hoja de cálculo no son visibles, puede copiar todas las celdas (o solo las visibles). De forma predeterminada, Excel copia no sólo las celdas visibles, sino también las ocultas o filtradas. Si solo desea copiar celdas visibles, siga los pasos a continuación. Por ejemplo, puede copiar sólo datos resumidos de una hoja de trabajo estructurada.

Siga los pasos a continuación.

Nota: Al copiar, los valores se insertan secuencialmente en filas y columnas. Si el área para pegar contiene filas o columnas ocultas, es posible que deba mostrarlas para ver todos los datos que copió.

Cuando copia y pega celdas visibles en un rango de datos que contiene celdas ocultas o que tiene un filtro aplicado, puede notar que las celdas ocultas se pegan junto con las celdas visibles. Desafortunadamente, no puede cambiar esta configuración cuando copia y pega un rango de celdas en Excel para la web porque pegar solo celdas visibles no está disponible.

Sin embargo, si formatea sus datos como una tabla y aplica un filtro, puede copiar y pegar solo las celdas visibles.

Si no desea formatear sus datos como una tabla y tiene instalado Excel de escritorio, puede abrir el libro en Excel para copiar y pegar celdas visibles. Para hacer esto, haga clic en el botón Abrir en Excel y siga los pasos en Copiar y pegar solo celdas visibles.

Más información

Siempre puede hacer una pregunta a un especialista de Excel Tech Community, pedir ayuda en la comunidad de Respuestas y también sugerir nueva característica o mejora en el sitio web

La selección de datos en Excel se realizó mediante un filtro o clasificación. Ahora es necesario imprimirlos o trasladarlos a otra ubicación. Copiar a Excel configurado para que las celdas ocultas también se copien.
Consideremos dos formas, A Cómo copiar filas filtradas en Excel.
Primera manera.
Hay una gran función en Excel: Función Pegar especial en Excel.
Entonces tenemos una mesa.
Cómo instalar un filtro, consulte el artículo " Filtrar en Excel ".
Usamos un filtro para eliminar todos los Ivanov de la lista. Resultó así.
Seleccione la tabla y haga clic en "Copiar" en el menú contextual. Haga clic izquierdo en la celda A9 y seleccione "valores" en el menú contextual.
Haga clic en "Aceptar". Listo. No sólo se copió el valor de las filas visibles, sino también el formato de las celdas.
Hay un matiz- insertar datos filtrados no en las filas donde se encuentra el filtro. Por ejemplo, en nuestro ejemplo, no en las líneas 1 a 7, sino debajo o en otra hoja, etc. Si pegamos en las filas donde se encuentra el filtro, los datos filtrados también se insertarán en las filas ocultas por el filtro. En general, resultará un desastre. Segunda vía.
La mesa es la misma. Seleccione la tabla con datos filtrados. En la pestaña “Inicio”, haga clic en la sección “Edición” Buscar y resaltar funciones en Excel. Luego, haga clic en el botón "Ir". En el cuadro de diálogo que aparece, haga clic en el botón "Seleccionar...". En la ventana "Seleccionar un grupo de celdas", marque la casilla junto a "solo celdas visibles". Haga clic en "Aceptar". Ahora en la misma tabla seleccionada con el botón derecho del mouse llamamos menú contextual. Haga clic en la función "Copiar". En una nueva ubicación (en nuestro ejemplo, esta es la celda A15), haga clic en "Insertar". Todo. Resultó así.
Cómo, sin copiar, inmediatamente. imprimir datos del filtro en excel, consulte el artículo "Marcar hoja de excel"Diseño de página"

Pegar sólo en las líneas visibles enSobresalir números, fórmulas y texto se pueden hacer de varias maneras. Cuando necesite insertar números, fórmulas o texto en no todas las filas de la tabla, puede utilizar un filtro. Cómo instalar un filtro y cómo filtrar en Excel, consulte el artículo “Filtro en Excel”. Pero para insertar datos sólo en celdas visibles, necesita sus propios métodos, especialmente si hay muchas filas.
La primera manera es común .
Tomemos una mesa como esta. La tabla será la misma para todos los ejemplos.
Usemos un filtro para eliminar todos los dígitos 2 de la tabla. En las celdas visibles restantes ponemos el número 600. En la celda B2 ponemos el número 600, luego lo copiamos hacia abajo en la columna (tira de la esquina inferior derecha de la celda B2). Los valores se copiaron sólo en las celdas visibles. También puedes insertar fórmulas de la misma forma. Escribimos la siguiente fórmula en la celda C2. =A2*10
Resultó así.
Cancelemos el filtro. El resultado es una tabla como esta.
La fórmula y los números se insertaron solo en las filas filtradas.
Segunda vía.
También filtraremos los datos. En la primera celda escribimos un número, fórmula, texto, etc. Ahora, si hay miles de filas, seleccione las celdas de esta manera: presione las teclas "Ctrl" + "Shift" + el botón de flecha hacia abajo (o el botón hacia arriba, dependiendo de dónde queramos seleccionar las celdas, abajo o arriba). la celda en la que se escribió el número).
Ahora, o presione la combinación de teclas “Ctrl” + G, o la tecla F5. Aparecerá el cuadro de diálogo Transición. Haga clic en el botón "Seleccionar...". Y, en el nuevo cuadro de diálogo "Seleccionar un grupo de celdas", marque la casilla junto a las palabras "Solo celdas visibles".Haga clic en "Aceptar". Luego insértelo como de costumbre.

Otra forma de abrir el cuadro de diálogo Seleccionar grupo de celdas.En la pestaña "Inicio", en la sección "Edición", haga clic en el botón "Buscar y seleccionar". En la lista que aparece, haga clic en la función “Seleccionar un grupo de celdas”.

A llenar celdas visibles en columnas seleccionadas de Excel, presione la combinación de teclas “Ctrl” + D. Y todas las columnas seleccionadas se completarán con datos o una fórmula, como en la primera celda. En nuestro ejemplo, escribimos el número 800 en la celda D2, columna D.

Tercera vía.
En una nueva columna (en nuestro ejemplo, columna E), seleccione las celdas. Presione la tecla F5. Aparecerá el cuadro de diálogo Transición. Haga clic en el botón "Seleccionar...". Y, en el nuevo cuadro de diálogo "Seleccionar un grupo de celdas", marque la casilla junto a las palabras "Solo celdas visibles". Haga clic en "Aceptar". Ahora, sin cancelar la selección, en la primera celda de la columna (la nuestra es E2), ingresa una fórmula, número, etc. Presione la combinación de teclas “Ctrl” + “Entrar”.