Consejo: Otros idiomas son traducidos por Google. Puedes visitar el English versión de este enlace.
Iniciar sesión
x
or
x
x
Suscríbete
x

or

¿Cómo insertar fila automáticamente en función del valor de la celda en Excel?

doc-insert-row-based-on-value-1
Supongamos que tiene un rango de datos, y desea insertar automáticamente filas en blanco por encima o por debajo de un cierto valor en Excel, por ejemplo, insertar filas automáticamente debajo del valor cero como se muestra a continuación. En Excel, no hay una forma directa de resolver esta tarea, pero puedo introducir un código Macro para que inserte filas automáticamente en función de un valor determinado en Excel.
Insertar fila a continuación según el valor de celda con VBA

Insertar fila arriba según el valor de la celda con Kutools para Excel buena idea3

Seleccione Celdas / Filas / Columnas con uno o dos criterios en Excel

Las celdas específicas seleccionadas de Kutools para Excel pueden seleccionar rápidamente todas las celdas o filas o columnas en un rango basado en un criterio o dos criterios. ¡Haz clic para la versión de prueba gratuita de 60 días!
doc selecciona celula especifica xnumx
Kutools para Excel: con más de 300 útiles complementos de Excel, de prueba sin límite en días 60.

Para insertar una fila según el valor de celda ejecutando VBA, realice los pasos siguientes:

fórmula ¡Ya no tiene que preocuparse por fórmulas largas en Excel! Kutools for Excel's Texto automático puede agregar todas las fórmulas a un grupo
como texto automático, ¡y libera tu cerebro! Haga clic aquí para saber el texto automático Haga clic aquí para obtener una prueba gratuita

1. Prensa Alt + F11 llaves simultáneamente, y una Microsoft Visual Basic para aplicaciones ventana emergente.

2. Hacer clic recuadro > Módulo, luego pegue el código VBA debajo del popping Módulo ventana.

VBA: inserte la fila a continuación según el valor de la celda.

Sub BlankLine()
	'Updateby20150203
	Dim Rng As Range
	Dim WorkRng As Range
	On Error Resume Next
	xTitleId                   = "KutoolsforExcel"
	Set WorkRng                = Application.Selection
	Set WorkRng                = Application.InputBox("Range", xTitleId, WorkRng.Address, Type: = 8)
	Set WorkRng                = WorkRng.Columns(1)
	xLastRow                   = WorkRng.Rows.Count
	Application.ScreenUpdating = False
	For xRowIndex = xLastRow To 1 Step - 1
		Set Rng                   = WorkRng.Range("A" & xRowIndex)
		If Rng.Value = "0" Then
			Rng.Offset(1, 0).EntireRow.Insert Shift: = xlDown
		End If
	Next
	Application.ScreenUpdating = True
End Sub

3. Hacer clic F5 clave o el corrida botón, aparece un cuadro de diálogo y selecciona la columna que contiene cero. Ver captura de pantalla:
doc-insert-row-based-on-value-2

4. Hacer clic OK. Entonces, las filas en blanco se insertarán debajo del valor cero.
doc-insert-row-based-on-value-3

Tip:

1. Si desea insertar filas basadas en otro valor, puede cambiar 0 a cualquier valor que desee en el VBA: Si Rng.Value = "0" Entonces.

2. Si desea insertar filas por encima de cero u otro valor, puede usar el siguiente código vba.

VBA: Insertar fila por encima del valor cero:

Sub BlankLine()
	'Updateby20150203
	Dim Rng As Range
	Dim WorkRng As Range
	On Error Resume Next
	xTitleId                   = "KutoolsforExcel"
	Set WorkRng                = Application.Selection
	Set WorkRng                = Application.InputBox("Range", xTitleId, WorkRng.Address, Type: = 8)
	Set WorkRng                = WorkRng.Columns(1)
	xLastRow                   = WorkRng.Rows.Count
	Application.ScreenUpdating = False
	For xRowIndex = xLastRow To 1 Step - 1
		Set Rng                   = WorkRng.Range("A" & xRowIndex)
		If Rng.Value = "0" Then
			Rng.EntireRow.Insert Shift: = xlDown
		End If
	Next
	Application.ScreenUpdating = True
End Sub

doc-insert-row-based-on-value-4


Si no está familiarizado con VBA, puede intentarlo Kutools for Excel's Seleccionar celdas específicas utilidad, y luego insertar filas arriba.

Kutools for Excel, con más de 300 Funciones prácticas, hace que sus trabajos sean más fáciles.

Después de instalar Kutools para Excel, haz lo siguiente:(Descarga gratuita Kutools for Excel ahora!)

1. Seleccione la lista de la que desea averiguar las celdas específicas y haga clic en Kutools > Seleccionar > Seleccionar celdas específicas. Ver captura de pantalla:
fila de inserción doc basada en el valor 9

2. En el cuadro de diálogo emergente, verifique Fila completa opción, y luego ir a seleccionar Equivale Desde Tipo específico enumere, y luego ingrese el valor que desea encontrar en el cuadro de texto de la derecha. Ver captura de pantalla:
fila de inserción doc basada en el valor 6

3. Hacer clic Ok, y aparece un cuadro de diálogo para recordarle el número de filas seleccionadas, simplemente ciérrelo.

4. Coloque el cursor en una fila seleccionada, y haga clic con el botón derecho para seleccionar recuadro del menú de contexto. Ver captura de pantalla:
fila de inserción doc basada en el valor 7

Ahora las filas se insertan arriba en base a un valor específico.
fila de inserción doc basada en el valor 8


Artículos relativos

Exploración y edición con pestañas de múltiples libros de trabajo de Excel / documentos de Word como Firefox, Chrome, Internet Explore 10.

Es posible que esté familiarizado con la vista de múltiples páginas web en Firefox / Chrome / IE, y cambie entre ellas haciendo clic en las pestañas correspondientes fácilmente. Aquí, Office Tab admite un procesamiento similar, que le permite explorar múltiples libros de Excel o documentos de Word en una ventana de Excel o en una ventana de Word, y cambiar fácilmente entre ellos haciendo clic en sus pestañas. Haga clic gratis para 45-day trial de Office Tab!

ot excel

Kutools for Excel: la mejor herramienta de productividad de Office aumenta su productividad en un 80%

  • Super Formula Bar (edite fácilmente varias líneas de texto y fórmula); Diseño de lectura (lee y edita fácilmente un gran número de celdas); Pegar en rango filtrado...
  • Combinar celdas / filas / columnas y mantener datos; Contenido de celdas divididas; Combinar filas duplicadas y suma / promedio... Prevenir células duplicadas; Comparar rangos...
  • Seleccione Duplicado o Único Filas; Seleccionar filas en blanco (todas las celdas están vacías); Super Find y Fuzzy Find en muchos libros de trabajo; Selección aleatoria ...
  • Copia exacta Celdas múltiples sin cambiar la referencia de fórmula; Crear referencias automáticamente a múltiples hojas; Insertar viñetas, Casillas de verificación y más ...
  • Fórmulas favoritas e insertadas rápidamente, Gamas, cuadros y cuadros; Cifrar celdas con contraseña Crear una lista de correo y enviar correos electrónicos ...
  • Extracto del texto, Agregar texto, Eliminar por posición, Eliminar espacio; Crear e imprimir subtotales de paginación; Convertir entre contenido de celdas y comentarios...
  • Súper filtro (guardar y aplicar esquemas de filtro a otras hojas); Clasificación avanzada por mes / semana / día, frecuencia y más; Filtro especial por negrita, cursiva ...
  • Combinar libros de trabajo y hojas de trabajo; Combinar tablas basadas en columnas clave; Dividir datos en varias hojas; Conversión por lotes xls, xlsx y PDF...
  • Funciona con Office 2007-2019 y 365, y es compatible con todos los idiomas. Es fácil de implementar en su empresa. Funciones completas de prueba gratuita de 60-day.
pestaña kte 201905

Office Tab lleva la interfaz con pestañas a Office y hace que su trabajo sea mucho más fácil

  • Habilitar la edición y lectura con pestañas en Word, Excel, PowerPoint, Editor, Acceso, Visio y Proyecto.
  • Abra y cree varios documentos en nuevas pestañas de la misma ventana, en lugar de en nuevas ventanas.
  • ¡Aumenta tu productividad en un 50% y reduce cientos de clics de ratón por ti todos los días!
fondo officetab
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    safa · 3 months ago
    Hello, this is very helpful. What if I wanted to add two lines below and I wanted to more values. For instance, I want to add two lines after value 26/04/2019 and then two lines after 03/04/2019, and list goes on. How do I keep adding to the vba? Sorry im still a beginner. Thanks in advance.
  • To post as a guest, your comment is unpublished.
    Gina · 8 months ago
    Hi, Thanks for this, however I have another scenario where I need to insert a cingle cell under the value that is not zero. Appreciate any suggestion.
  • To post as a guest, your comment is unpublished.
    Louis · 11 months ago
    Hi i am trying to use this code to enter a row when a the first 4 digits in a cell changes (if thats even possible)

    for example,
    2222A
    2222B
    2223K


    the line will be inserted after 2222B as the 3rd number is a 3 and not a 2

    Thanks guys!!
  • To post as a guest, your comment is unpublished.
    Hi · 1 years ago
    hi I just wanna ask how to add row if the codition is that add row should be done when a cell has a data already (It is for a excel workbook with a lot of sheets :) Thanks!
    • To post as a guest, your comment is unpublished.
      Sunny · 1 years ago
      maybe this vba code can help you. It will add rows if above row is not empty

      Sub helping()
      Dim count As Long
      For count = ActiveSheet.UsedRange.Rows.count To 1 Step -1
      If Information.IsEmpty(Cells(count, 1)) = False Then Rows(count + 1).Insert
      Next count
      End Sub
  • To post as a guest, your comment is unpublished.
    ermias · 1 years ago
    Here is my question and it is a very difficult one i guess.. is there a vba code that add a new row below a filtered column and copy just the first three cells into the added new row and continue doing so until the user stops hitting "enter" and unfilter the filtered cells?
  • To post as a guest, your comment is unpublished.
    Tiago Dias · 1 years ago
    I need huge help on this subject. I have 2 columns, on the 1st I have my data time 01/01/2016 05:00:00, days/months/year hour/minute/seconds and in the 2 2nd column the respective data associated to the time.

    My problem is that I want to add data time between rows since I have days gaps. 1st line is 01/01/2016 and the 2nd row has, for example, 10/01/2016, so I have 9 days. and that code doesn't work for me.

    Looking forward to getting some feedback, please! Thanks
    • To post as a guest, your comment is unpublished.
      Sunny · 1 years ago
      You can try this VBA

      Sub InsertValueBetween()
      'Update 20130825
      Dim WorkRng As Range
      Dim Rng As Range
      Dim outArr As Variant
      Dim dic As Variant
      Set dic = CreateObject("Scripting.Dictionary")
      'On Error Resume Next
      xTitleId = "KutoolsforExcel"
      Set WorkRng = Application.Selection
      Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
      num1 = WorkRng.Range("A1").Value
      num2 = WorkRng.Range("A" & WorkRng.Rows.Count).Value
      interval = num2 - num1
      ReDim outArr(1 To interval + 1, 1 To 2)
      For Each Rng In WorkRng
      dic(Rng.Value) = Rng.Offset(0, 1).Value
      Next
      For i = 0 To interval
      outArr(i + 1, 1) = i + num1
      If dic.Exists(i + num1) Then
      outArr(i + 1, 2) = dic(i + num1)
      Else
      outArr(i + 1, 2) = ""
      End If
      Next
      With WorkRng.Range("A1").Resize(UBound(outArr, 1), UBound(outArr, 2))
      .Value = outArr
      .Select
      End With
      End Sub


      Or if you have Kutools for Excel, you can try this function:
      • To post as a guest, your comment is unpublished.
        Tiago Dias · 1 years ago
        Hello again Sunny, i got some sucess on editing the code to this (i change the num1 line to A2 and With WorkRng.Range("A2:A100000").Resize(UBound(outArr, 1), UBound(outArr, 2)):


        Sub InsertValueBetween()
        'Update 20130825
        Dim WorkRng As Range
        Dim Rng As Range
        Dim outArr As Variant
        Dim dic As Variant
        Set dic = CreateObject("Scripting.Dictionary")
        'On Error Resume Next
        xTitleId = "KutoolsforExcel"
        Set WorkRng = Application.Selection
        Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
        num1 = WorkRng.Range("A2").Value
        num2 = WorkRng.Range("A" & WorkRng.Rows.Count).Value
        interval = num2 - num1
        ReDim outArr(1 To interval + 1, 1 To 2)
        For Each Rng In WorkRng
        dic(Rng.Value) = Rng.Offset(0, 1).Value
        Next
        For i = 0 To interval
        outArr(i + 1, 1) = i + num1
        If dic.Exists(i + num1) Then
        outArr(i + 1, 2) = dic(i + num1)
        Else
        outArr(i + 1, 2) = ""
        End If
        Next
        With WorkRng.Range("A2:A100000").Resize(UBound(outArr, 1), UBound(outArr, 2))
        .Value = outArr
        .Select
        End With
        End Sub



        I show you the graphs, it doesn't work 100% because it doesn't create the time from A1 to A2
      • To post as a guest, your comment is unpublished.
        Tiago Dias · 1 years ago
        Thanks a lot, I have tried both, the 1st one since I have like 500 rows of data, I do that for the all 500 rows and doesn't do anything, I think perhaps it has a limitation on the rows to use, and when I select just the first 5 rows, for example, it doesn't create the missing rows, replaces the rows for the missing data.

        Another problem that I have is that my time data has also the Day/Month/Year HH: MM: SS
        • To post as a guest, your comment is unpublished.
          Tiago Dias · 1 years ago
          From 2 to 3, it creates the missing data that I want ok, but the value of the 03/01/2016 is eliminated and there is some time data that is eliminated something that I don't want either
          • To post as a guest, your comment is unpublished.
            Sunny · 1 years ago
            Sorry the VBA code did not help you, I cannot find the method which can work for date and time format. If you find the solution finnally, could you let me know? Thank you.
  • To post as a guest, your comment is unpublished.
    Faissal sardar · 1 years ago
    How Can I insert more than one row ?
    • To post as a guest, your comment is unpublished.
      Sunny · 1 years ago
      if you want to insert blank rows below, try this

      Sub BlankLine()
      'Updateby20150203
      Dim Rng As Range
      Dim WorkRng As Range
      Dim xInsertNum As Long
      ' On Error Resume Next
      xTitleId = "Kutools for Excel"
      Set WorkRng = Application.Selection
      Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
      If WorkRng Is Nothing Then Exit Sub
      xInsertNum = Application.InputBox("The number of blank rows you want to insert", xTitleId, Type:=1)
      If xInsertNum = False Then
      MsgBox " The number of blank rows you want to insert ", vbInformation, xTitleId
      Exit Sub
      End If
      Set WorkRng = WorkRng.Columns(1)
      xLastRow = WorkRng.Rows.Count
      Application.ScreenUpdating = False
      For xRowIndex = xLastRow To 1 Step -1
      Set Rng = WorkRng.Range("A" & xRowIndex)
      If Rng.Value = "0" Then
      Rng.Offset(1, 0).Resize(xInsertNum).EntireRow.Insert Shift:=xlDown
      End If
      Next
      Application.ScreenUpdating = True
      End Sub

      The below one is to insert rows above.
      • To post as a guest, your comment is unpublished.
        Joubero Lambrechts · 4 months ago
        HI Sunny, this macro works perfectly for me; i just had to change the quantity of rows to 30 and change the 0 to text: "Closing Balance". But now i want to copy paste a selection of cells which is 30 rows high into the 30 blank lines which were just inserted by this macro. Can you suggest a new macro (or an amendment to this one) to copy and paste a range into each 30blanks lines. I have named the range to copy and paste 'template'.
    • To post as a guest, your comment is unpublished.
      Sunny · 1 years ago
      You can try this VBA

      Sub BlankLine()
      'Updateby20150203
      Dim Rng As Range
      Dim WorkRng As Range
      Dim xInsertNum As Long
      ' On Error Resume Next
      xTitleId = "Kutools for Excel"
      Set WorkRng = Application.Selection
      Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
      If WorkRng Is Nothing Then Exit Sub
      xInsertNum = Application.InputBox("The number of blank rows you want to insert ", xTitleId, Type:=1)
      If xInsertNum = False Then
      MsgBox " The number of blank rows you want to insert ", vbInformation, xTitleId
      Exit Sub
      End If
      Set WorkRng = WorkRng.Columns(1)
      xLastRow = WorkRng.Rows.Count
      Application.ScreenUpdating = False
      For xRowIndex = xLastRow To 1 Step -1
      Set Rng = WorkRng.Range("A" & xRowIndex)
      If Rng.Value = "0" Then
      Rng.Resize(xInsertNum).EntireRow.Insert Shift:=xlDown
      End If
      Next
      Application.ScreenUpdating = True
      End Sub
  • To post as a guest, your comment is unpublished.
    pranav · 1 years ago
    That was amaaaazing!!. Thanks man.
  • To post as a guest, your comment is unpublished.
    Isidora · 1 years ago
    I love you. Thank you.
  • To post as a guest, your comment is unpublished.
    yatorres90 · 1 years ago
    I want to insert rows based on a count using a cell value in one spreadsheet and inserting rows in another spreadsheet.
    • To post as a guest, your comment is unpublished.
      Sunny · 1 years ago
      Thanks to your message. But can you discribe your question with more details? What rows do you want to insert? Blank? And Where you want to insert at in the sheet? If you can, give me some screenshhot. Thank you.
  • To post as a guest, your comment is unpublished.
    Rahul · 2 years ago
    Hi,
    I want to insert multiple rows based on the value
    Ex: I want to insert 1 blank row below the cell with value 2, 2 rows below the cell with value 3, 3 rows below the cell with value 4 and so on

    Can you please help me with this?
    • To post as a guest, your comment is unpublished.
      Ashley · 1 years ago
      DId you ever get an answer to this? I'm trying to do the same thing.

      Have a list of employees with # of weeks vacation they get. I want to insert a row for each week. It will be 1, 2 or 3 rows depending on how much time they've earned. the #s 1 2 3 are already in my spreadsheet.
  • To post as a guest, your comment is unpublished.
    Jafar · 4 years ago
    I want to paste specific content under below cell. How to do that? Instead of Blank row, I want to insert value in few columns.