VBA command button to insert row at bottom of table using active.cell

[charliew]

Good evening,

I have a table that sometimes i might need to add a row to. I need this row to insert at the bottom of the active cell and still hold the formatting and formulas from the row above it, without the contents of the cell.

I did a record macro as i got fed up trying to figure it out. It works if all i wanted to do was add one row. But i might want to add more than 1 row, but when i click the button again the new row add in the middle of my table and not at the bottom.

Here is my code I'm using.

Sub Add_Lines()'' Add_Lines Macro'
'    Rows("19:19").Select    
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove    
Range("A18:T18").Select    
Selection.Copy    
Range("A19:T19").Select    
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False    
Application.CutCopyMode = False    
Range("A18:Q18").Select    
Selection.Copy    
Range("A19:Q19").Select    
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _        
SkipBlanks:=False, Transpose:=False    
Range("B19:D19").Select    
Application.CutCopyMode = False    
Selection.ClearContents
End Sub

ideally, it would be nice if a message box pops up asking how many rows id like to enter...

thanks in advance

 

[DanteAmor]

If you really converted a range into a table,

then you can insert a row like this:

Sub test_1()
  ActiveSheet.ListObjects("Table1").ListRows.Add AlwaysInsert:=True
End Sub

To insert 5 rows:

Sub test_5()
  Dim tbl As ListObject, rng As Range
  Set tbl = ActiveSheet.ListObjects("Table1")
  Set rng = Range("Table1[#All]").Resize(tbl.Range.Rows.Count + 5, tbl.Range.Columns.Count)
  tbl.Resize rng
End Sub

With inputbox:

Sub test_n()
  Dim tbl As ListObject, rng As Range, n As Variant
  n = InputBox("How many rows:", "INSERT ROWS")
  If n = "" Or Not IsNumeric(n) Or n < 1 Then Exit Sub
  If Int(n) < Val(n) Then Exit Sub
  Set tbl = ActiveSheet.ListObjects("Table1")
  Set rng = Range("Table1[#All]").Resize(tbl.Range.Rows.Count + n, tbl.Range.Columns.Count)
  tbl.Resize rng
End Sub

 

[charliew]

Thank you, sorry i should have explained better. It's not a table, it is a range of data. A14:T18 including headers. A table isn't possible as i need some cells to be merged.

 

[DanteAmor]

There is a big difference between a table and a range of cells.

I have a table that sometimes i might need to add a row to...
...when i click the button again the new row add in the middle of my table and not at the bottom.

Let us begin.

Please, could you answer the following:


- Is the row or rows always inserted at the end of the range used?
- Which of your columns always has data?
- And which of the rows has the formats and formulas to copy and paste it in the new row?

 

[charliew]

I apologise an unintentional slip in terminology.

A14:T18 including headers. is my data range

A row is filled out per item to be delivered, if there are more than 4 items i need a new row. Row 18 is the last formatted row and this will always be filled in when a new row is inserted. however, sometimes when the new row inserted has been filled out i might need to add an additional row, this also will need adding to the bottom of the data range now including row 19.

the row always has to be added at the bottom of the data range.

Column K has conditional formatting that is imperative to the spreadsheet.

Apologies for the rocky start and the time wasted, i really hope you can help me with this. Tried to have another go myself but i just can't get it to do both things.

I appreciate your time.

 

[DanteAmor]

I think you didn't answer my questions. You could put a picture of your entire sheet.

 

[charliew]

image should be here

https://ibb.co/hdv3brf

thanks again for your time

 

[DanteAmor]

Try his

Sub Macro1()
  Dim i As Long, n As Variant
  n = InputBox("How many rows:", "INSERT ROWS")
  If n = "" Or Not IsNumeric(n) Or n < 1 Then Exit Sub
  If Int(n) < Val(n) Then Exit Sub
  i = 15
  Do While Cells(i, "B") <> ""
    i = i + 1
  Loop
  Rows(i & ":" & i + n - 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
  Rows(i - 1 & ":" & i - 1).Copy
  Rows(i & ":" & i + n - 1).PasteSpecial Paste:=xlPasteFormulas
  Rows(i & ":" & i + n - 1).PasteSpecial Paste:=xlPasteFormats
  Application.CutCopyMode = False
End Sub

 

[charliew]

Thank you, it works almost perfectly, apart from it copies the contents of the above cell, when i need these to be blank cells. Apart from that, spot on! thank you so much!

 

[DanteAmor]

Thank you, it works almost perfectly, apart from it copies the contents of the above cell, when i need these to be blank cells. Apart from that, spot on! thank you so much!

Revisa tus fórmulas, solo se están copiando formatos y fórmulas.
Si no quieres copiar fórmulas, entonces quita esta línea:

Rows(i & ":" & i + n - 1).PasteSpecial Paste:=xlPasteFormulas

If you want to copy validationes, try this:

Rows(i & ":" & i + n - 1).PasteSpecial Paste:=xlPasteValidation