Home / Questions / 473907
Accepted
victor julio
Asked: 2022-07-30 12:17:53 +0800 CST 2022-07-30 12:17:53 +0800 CST

How can I make this macro not insert empty rows in another sheet of the workbook?

  • 772

The macro has this problem:

  • If Monica buys only flowers (see image 1), then on Sheet 2 she inserts 4 empty rows.

  • If Monica buys only flowers and wrapping paper, then on Sheet 2 she inserts 3 empty rows.

  • If Monica buys only flowers, gift wrap and a card, then on Sheet 2 she inserts 2 rows (see image 2).

How can I make it so that the macro does not insert empty rows in Sheet 2?

Table "Customer Purchases"

Table "Sales List"

Sub Compras_del_Cliente()

    'Compras del cliente Macro
    Sheets("Hoja2").Select
    Range("A9:A13").EntireRow.Insert

    'Nombre / Empresa
    Sheets("Hoja1").Select
    Range("C9").Copy      
    Sheets("Hoja2").Select
    Range("A9").PasteSpecial xlPasteValues

    'Código
    Sheets("Hoja1").Select
    If Range("B12:B16").Select <> Empty Then
        Range("B12:B16").Copy 
    End If
    Sheets("Hoja2").Select
    Range("C9").PasteSpecial xlPasteValues

    'Descripción del Producto
    Sheets("Hoja1").Select
    If Range("C12:C16").Select <> Empty Then
        Range("C12:C16").Copy    
    End If
    Sheets("Hoja2").Select
    Range("B9").PasteSpecial xlPasteValues

    'Cantidad
    Sheets("Hoja1").Select
    If Range("D12:D16").Select <> Empty Then
        Range("D12:D16").Copy    
    End If
    Sheets("Hoja2").Select
    Range("D9").PasteSpecial xlPasteValues

    'Precio
    Sheets("Hoja1").Select
    If Range("E12:E16").Select <> Empty Then
        Range("E12:E16").Copy    
    End If
    Sheets("Hoja2").Select
    Range("E9").PasteSpecial xlPasteValues

End Sub

Sub Borrar()
    'Botón para borrar valores en la tabla "Compras del CLIENTE" en Hoja 1
    Range("C9").Value = Empty
    Range("B12:B16").Value = Empty
    Range("C12:C16").Value = Empty
    Range("D12:D16").Value = Empty
    Range("E12:E16").Value = Empty
End Sub

Sub Eliminar_venta()
    'Botón para eliminar venta en la tabla "Lista de VENTAS" en la Hoja 2
    Sheets("Hoja2").Select
    Selection.EntireRow.Delete
End Sub
vba

1 Answers

  1. Best Answer
        'Código
    Sheets("Hoja1").Select
    If Range("B12:B16").Select <> Empty Then
        Range("B12:B16").Copy 
    End If
    Sheets("Hoja2").Select
    Range("C9").PasteSpecial xlPasteValues

    In this part of the code, you are saying that if there is any data in the range of cells, from B12 to B16, that is, it is different from Empty, copy the entire range and paste it on sheet 2 from cell C9

    You are not saying to copy the cells with data, you are saying that if there is data in any cell, copy them all.

    You are copying 5 rows regardless of whether there is data in one (flowers), two (flowers and paper) or three (flowers, paper and cards). Since you copy the five rows, it will paste the rows with data and the empty rows, hence it will paste those blank lines that you say.

    The way to avoid it, would be to evaluate row by row, if there is data that you copy the cell with data.

        'Código
    Sheets("Hoja1").Select
    If Range("B12").Select <> Empty Then
        Range("B12").Copy 
    End If
    Sheets("Hoja2").Select
    Range("C9").PasteSpecial xlPasteValues

    You could create a loop that validates that in the range of rows it finds data, if it finds it, copy it and paste it on sheet 2

    'seleccionamos la hoja donde se copiarán los datos
Sheets("Hoja1").Select
'declaramos variables de rango y celdas en el rango
Dim rng As Range, cell As Range
'metemos el rango B12:B16 en la variable de rango rng
Set rng = Range("B12:B16")
'hacemos un bucle del rango
For Each cell In rng
    'volvemos a selecionar la hoja a copiar para las iteraciones del bucle
    Sheets("Hoja1").Select
    'si la celda seleccionada que corresponde con el bucle no está vacía
    If cell.Select <> Empty Then
        'la copiamos
        cell.Copy
    End If
    'seleccionamos la hoja 2
    Sheets("Hoja2").Select
    'si la celda 9 de la fila 3 (C9) está vacía
    If IsEmpty(Cells(9, 3)) Then
        'pegamos lo que se había copiado
        Cells(9, 3).PasteSpecial xlPasteValues
        'si la anterio no está vacía pero si lo está la siguiente
        ElseIf IsEmpty(Cells(10, 3)) Then
            Cells(10, 3).PasteSpecial xlPasteValues
            ElseIf IsEmpty(Cells(11, 3)) Then
                Cells(11, 3).PasteSpecial xlPasteValues
                ElseIf IsEmpty(Cells(12, 3)) Then
                    Cells(12, 3).PasteSpecial xlPasteValues
                    ElseIf IsEmpty(Cells(13, 3)) Then
                        Cells(13, 3).PasteSpecial xlPasteValues
                        ElseIf IsEmpty(Cells(14, 3)) Then
                            Cells(14, 3).PasteSpecial xlPasteValues
                        End If
Next cell
    • 3