Macro de Excel: pegue solo celdas no vacías de una hoja a otra

A continuación se muestra el código que estoy usando para copiar celdas de una hoja y pegarlas en otra.

Sheets("codes").Select Range("A5:A100").Select Selection.Copy Sheets("Sheet2").Select Range("B28").Select ActiveSheet.Paste 

El problema con esto es que algunas celdas en este rango están en blanco, pero no quiero que se copien en Sheet2. Tengo una idea de aquí, pero este método es demasiado largo. ¿Hay alguna manera de iterar en la selección y verificar si el valor no está vacío y pegar? De esta forma también puedo pegar otro texto (por ejemplo, #NA) en las celdas en blanco.

Parece que puedes estar cometiendo algunos errores comunes de novato aquí (está bien que todos lo hayamos hecho).


Ejemplo VBA con explicaciones línea por línea

SUGERENCIA: intente no usar “Seleccionar” o “Copiar”. ¿Por qué usar seleccionar cuando todo lo que tiene que hacer es hacer referencia a las celdas? Por ejemplo, en lugar de usar

 Sheets("codes").Select Range("A5:A100").Select Selection.Copy Sheets("Sheet2").Select Range("B28").Select ActiveSheet.Paste 

Solo usa

 dim mySheet as Worksheet, myOtherSheet as Worksheet, myBook as Workbook 'Define your workbooks and worksheets as variables set myBook = Excel.ActiveWorkbook set mySheet = myBook.Sheets("codes") set myOtherSheet = myBook.Sheets("Sheet2") dim i as integer, j as integer 'Define a couple integer variables for counting j = 28 'This variable will keep track of which row we're on in Sheet2 (I'm assuming you want to start on line 28) for i = 5 to 100 'This is the beginning the the loop which will repeat from 5 to 100 . . . if mySheet.Cells(i,1).value <> "" then ' . . . for each digit, it will check if the cell's value is blank. If it isn't then it will . . . myOtherSheet.Cells(j,2).value = mySheet.Cells(i,1).value ' . . . Copy that value into the cell on Sheet2 in the row specified by our "j" variable. j = j + 1 'Then we add one to the "j" variable so the next time it copies, we will be on the next available row in Sheet2. end if next i 'This triggers the end of the loop and moves on to the next value of "i". 

Hice lo mismo todo el tiempo cuando comencé, y nunca funciona bien. “Seleccionar” causa errores a la izquierda y a la derecha. Usa mi código, lee los comentarios y estarás bien. Una ADVERTENCIA rápida: no tengo Excel en esta computadora, así que no pude probar el código. Si no funciona por alguna razón, déjame un comentario y mañana lo arreglaré en el trabajo.

El código anterior omitirá las celdas en blanco por completo al copiar los datos en su segunda hoja. Si desea ingresar un texto determinado para celdas en blanco (como “N / A”), puede usar lo siguiente:

  dim mySheet as Worksheet, myOtherSheet as Worksheet, myBook as Workbook 'Define your workbooks and worksheets as variables set myBook = Excel.ActiveWorkbook set mySheet = myBook.Sheets("codes") set myOtherSheet = myBook.Sheets("Sheet2") dim i as integer, j as integer 'Define a couple integer variables for counting j = 28 'This variable will keep track of which row we're on in Sheet2 (I'm assuming you want to start on line 28) for i = 5 to 100 'This is the beginning the the loop which will repeat from 5 to 100 . . . if mySheet.Cells(i,1).value <> "" then ' . . . for each digit, it will check if the cell's value is blank. If it isn't then it will . . . myOtherSheet.Cells(j,2).value = mySheet.Cells(i,1).value ' . . . Copy that value into the cell on Sheet2 in the row specified by our "j" variable. else 'If the cell is blank, then . . . myOtherSheet.Cells(j,2).value = "N/A" ' . . . place the text "N/A" into the cell in row "j" in Sheet2. end if 'NOTICE we moved the "end if" statement up a line, so that it closes the "if" statement before the "j = j + 1" statement. _ This is because now we want to add one to the "j" variable (ie, move to the next available row in Sheet2) regardless of whether the cell in the "codes" sheet is blank or not. j = j + 1 'Then we add one to the "j" variable so the next time it copies, we will be on the next available row in Sheet2. next i 'This triggers the end of the loop and moves on to the next value of "i". 

Fácil:

  Sheet1.Range("A1:a500").SpecialCells(xlCellTypeConstants).Copy Sheet2.Range("b2") 

xlCellTypeConstants pero hay muchas otras posibilidades.

Sheet1 es generalmente equivalente a Sheets("Sheet1") . El primero es el nombre en el VBE (vista del progtwigdor), el segundo es el nombre en la interfaz del usuario (vista del usuario). Generalmente prefiero la primera syntax porque es más corta y permite cambiar el nombre de las hojas (para el usuario) sin afectar el código.

Si no necesitas formatear, usaría lo siguiente. Todo lo que hace es copiar el rango que especifica en la hoja de trabajo a una variable, recorrer esa variable, buscar celdas que están vacías y ponerlas en la cadena que desee. Es lindo y rápido Si desea conservar el formato, puede pegar solo formatos especiales en el rango de salida.

 Sub CopyNonBlankCells(rFromRange As Range, rToCell As Range, sSubIn As String) 'You have three inputs. A range to copy from (rFromRange), a range to copy to (rToCell) and a string to put in the blank cells. Dim vData As Variant, ii As Integer, jj As Integer 'Set to a variable since it's quicker vData = rFromRange.Value 'Loop through to find the blank cells For ii = LBound(vData, 1) To UBound(vData, 1) 'Loop the rows For jj = LBound(vData, 2) To UBound(vData, 2) 'Loop the columns 'Check for empty cell. Quicker to use Len function then check for empty string If VBA.Len(vData(ii, jj)) = 0 Then vData(ii, jj) = sSubIn Next jj Next ii 'Output to target cell. Use the 'With' statement because it makes the code easier to read and is more efficient With rToCell.Parent .Range(.Cells(rToCell.Row, rToCell.Column), .Cells(rToCell.Row + UBound(vData, 1) - 1, rToCell.Column + UBound(vData, 2) - 1)).Value = vData End With End Sub 

Y llámalo con:

 Call CopyNonBlankCells(Sheets("codes").Range("A5:A100"), Sheets("Sheet2").Range("B28"), "Non-blank")