I need some help with inserting and deleting rows in a table using vba in excel (1 Viewer)

david.paton · Jul 15, 2018

I have been asked by my boss to make a spreadsheet that is used for calculations. This spreadsheet has 3 lookup columns that change the calculations and 2 columns that measure units and number of workers required. These impact the price as well. I don't know how to code but I have managed to find some code that will delete rows. This is the code I have:

Sub Button6_Click()
Dim lastrow As Integer
lastrow = ActiveSheet.Range("table3").ListObject.Range.Rows.Count
ActiveSheet.Unprotect Password:="123"
Range("table3").ListObject.ListRows(lastrow - 1).Delete
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="123"

End Sub

I also have found some code to insert a new row too, but it won't copy formulas to the new cells.

Private Sub CommandButton2_Click()
Dim the_sheet As Worksheet
Dim table_list_object As ListObject
Dim table_object_row As ListRow

Set the_sheet = Sheets("Schedule 3 -Request for Service")
Set table_list_object = the_sheet.ListObjects(1)
Set table_object_row = table_list_object.ListRows.Add

End Sub

The only problem is that I have formulas in columns F, G, H and M that I need copied to the new row when it is created.

Could someone please help me with a way to add these formulas to these rows and also if there is a better way to accomplish the insert/delete rows.

In addition, the spreadsheet will be locked down so users can't change formulas or wreck it. How do I accomplish the above when the sheet is protected. I can't upload the spreadsheet for privacy reasons. I have been searching the net all weekend but I can't seem to find anything.

Thanks,
Dave

mike60smart · Jul 15, 2018

Hi Dave

I would create a macro to carry out what you need and apply the Macro to a Command Button.

david.paton · Jul 15, 2018

That's what I have done but I am not sure of the code.

CJ_London · Jul 15, 2018

In addition, the spreadsheet will be locked down so users can't change formulas or wreck it. How do I accomplish the above when the sheet is protected.

first bit of code - unlock the sheet
then run your code
then lock the sheet again when done

with regards copying formula, record a macro, do what you want to do then stop recording. Look at the macro and modify as required.

you'll find the record a macro option under the developer tab - if you can't see this tab, go to File>Options>Customise Ribbon and add it in there

david.paton · Jul 16, 2018

Are you saying to manually unlock the sheet? I wanted to have it locked, then be able to click a button to add or delete a row so I thought that you could have code that unlocked it, did what needed to be done then locked it again.

I can't just look at the code and modify it as I don't understand the code.

All I need is to be able to add or delete rows from a table, called table 3, in my sheet called "Schedule 3 -Request for Service". Could you help me with some code please?

CJ_London · Jul 16, 2018

sorry I'm not an excel guru

I can't just look at the code and modify it as I don't understand the code.

have you tried the record macro I suggested? You know each step you have taken so should not be difficult to determine what the code does - just do it is small steps until you have the confidence.

Gasman · Jul 16, 2018

I do not know what we did without Google, but a quick search for 'add row to excel table vba' gives http://www.bluepecantraining.com/po...ws-and-columns-to-excel-table-with-vba-macro/ at the top of many links ???

That is commented to explain the code.?

david.paton said:
Are you saying to manually unlock the sheet? I wanted to have it locked, then be able to click a button to add or delete a row so I thought that you could have code that unlocked it, did what needed to be done then locked it again.

I can't just look at the code and modify it as I don't understand the code.

All I need is to be able to add or delete rows from a table, called table 3, in my sheet called "Schedule 3 -Request for Service". Could you help me with some code please?

david.paton · Jul 16, 2018

Gasman said:
I do not know what we did without Google, but a quick search for 'add row to excel table vba' gives http://www.bluepecantraining.com/po...ws-and-columns-to-excel-table-with-vba-macro/ at the top of many links ???

That is commented to explain the code.?

I did find that table, thanks. I have worked out how to insert and delete rows but I am getting a lot REF errors in the formulas as they aren't being filled down when I make a new row. Does anyone know the vba to fill a formula down when a row is inserted?

Gasman · Jul 16, 2018

I just use something like below.
I know I have valid formulae in row 2, and I have calculated the start and end row numbers previously.

These are not tables though, just a normal worksheet.

HTH

Code:

 Application.StatusBar = "Copying formulae....."
    
    Range("S2").Copy ' Values
    Set rngCopyTo = Range("S" & lngStartClient & ":S" & lngLastClient)
    rngCopyTo.PasteSpecial Paste:=xlPasteFormulas
    rngCopyTo.NumberFormat = "$#,##0.00"
    
    Range("Y2").Copy ' Date key
    Set rngCopyTo = Range("Y" & lngStartClient & ":Y" & lngLastClient)
    rngCopyTo.PasteSpecial Paste:=xlPasteFormulas
    
    Range("M2:N2").Copy ' Status
    Set rngCopyTo = Range("M" & lngStartClient & ":N" & lngLastClient)
    rngCopyTo.PasteSpecial Paste:=xlPasteFormulas
    
    Range("W2").Copy ' Case Status
    Set rngCopyTo = Range("W" & lngStartClient & ":W" & lngLastClient)
    rngCopyTo.PasteSpecial Paste:=xlPasteFormulas

david.paton · Jul 16, 2018

I am going to try instead of the table, converting it to a range and see how I go.

david.paton · Jul 16, 2018

I am feeling a little confused. Should I leave it as a table or change it to a range?

david.paton · Jul 16, 2018

Gasman said:

I just use something like below.
I know I have valid formulae in row 2, and I have calculated the start and end row numbers previously.

These are not tables though, just a normal worksheet.

HTH

Code:

 Application.StatusBar = "Copying formulae....."
    
    Range("S2").Copy ' Values
    Set rngCopyTo = Range("S" & lngStartClient & ":S" & lngLastClient)
    rngCopyTo.PasteSpecial Paste:=xlPasteFormulas
    rngCopyTo.NumberFormat = "$#,##0.00"
    
    Range("Y2").Copy ' Date key
    Set rngCopyTo = Range("Y" & lngStartClient & ":Y" & lngLastClient)
    rngCopyTo.PasteSpecial Paste:=xlPasteFormulas
    
    Range("M2:N2").Copy ' Status
    Set rngCopyTo = Range("M" & lngStartClient & ":N" & lngLastClient)
    rngCopyTo.PasteSpecial Paste:=xlPasteFormulas
    
    Range("W2").Copy ' Case Status
    Set rngCopyTo = Range("W" & lngStartClient & ":W" & lngLastClient)
    rngCopyTo.PasteSpecial Paste:=xlPasteFormulas

This is the code I have for the Add a row button:

ActiveSheet.Unprotect Password:=""
Dim ws As Worksheet
Set ws = ActiveSheet
Dim tbl As ListObject
Set tbl = ws.ListObjects("table3")
'add a row at the end of the table
tbl.ListRows.Add
ActiveSheet.Protect Password:=""

Where do I put that code you described to copy the formulas?

Sorry, I don't know much vba.

david.paton · Jul 16, 2018

I have formulas in rows F, G, H and M, which all need copying down. They are all part of one table except M, which is part of it's own table.

Gasman · Jul 16, 2018

Well as you are adding the rows, why not just populate the cells at the same time
I am assuming that becomes the activecell row is the one added.and I've left it simple with one column at a time and the formula is in row 2 as I've done with my sheets.

HTH

Code:

tbl.ListRows.Add
Range("F"&Activecell.Row).Formula = Range("F2").Formula
Range("G"&Activecell.Row).Formula = Range("G2").Formula
Range("H"&Activecell.Row).Formula = Range("H2").Formula
Range("M"&Activecell.Row).Formula = Range("M2").Formula

david.paton · Jul 18, 2018

Thanks, that looks just like what I need. Only problem is, I don't know how to get it working. When I try and paste the code below behind a button, I get an error for tbl.ListRows.Add.

The error is Run time error 424, Object required

Does tbl needed to be declared as something?

Gasman · Jul 18, 2018

Have you set tbl ? is your table called table3?
Walk through the code with F8 and inspect the variables/objects

Code:

Set tbl = ws.ListObjects("table3")

david.paton · Jul 18, 2018

This spreadsheet records more then 1 item. This is the code I have behind my add button.

Private Sub CommandButton3_Click()
ActiveSheet.Unprotect Password:=""
Dim ws As Worksheet
Set ws = ActiveSheet
Dim tbl As ListObject
Set tbl = ws.ListObjects("table3")
'add a row at the end of the table
tbl.ListRows.Add
ActiveSheet.Protect Password:=""
End Sub

This is a screenshot of my spreadsheet
https://www.screencast.com/t/PN0tLWuHONGZ

This is the code behind my delete button

Private Sub CommandButton2_Click()
Dim iCntr As Long
Dim rng As Range
Set rng = ActiveSheet.ListObjects("Table3").Range
For iCntr = rng.Row + rng.Rows.Count - 1 To rng.Row Step -1
If Application.WorksheetFunction.CountA(Rows(iCntr)) = 0 Then Rows(iCntr).EntireRow.Delete
Next
End Sub

I have the data validation, lists in columns A, B and C.
D and E just have numbers entered by the user.
F contains =D7*E7*M7
G contains =F7*0.1
H contains =G7+F7
M contains =IF(OR(ISBLANK(A7),ISBLANK(B7),ISBLANK(C7)),0,VLOOKUP(CONCATENATE(A7," ",B7," ",C7),Service_Price_List,2,FALSE))

I have it all in the one table now. F, G and H fill down when I create a row but M doesn't.
I was thinking to have the formulas inserted into the row automatically when a new row is created. This would solve the problem where a user deletes all the rows and then there is no cells in the row above to fill down the formula to.
In addition, I also wanted it to prompt the user when deleting a row, if there is text in that row.
I think I explained it properly. Would be a lot easier if I could upload it but my manager doesn't want me to do that.

Thanks again for helping me!

Gasman · Jul 18, 2018

1. Do you mean Excel places the formula in the new row as soon as you add a row.?
That will likely be as it is defines it as a table (table3?).
If M is also a table, you need to add a row to that, otherwise put the foumula in M6, make the font white to hide it and then copy from M6 each time, but I think the table option is better, less work for you.
I've not used tables too much in automation, only ranges.

2. You either have to hard code the formula in the code or have somewhere to copy from. I prefer to copy as it saves all the RC working out, but if you record a macro for each, that should give you the syntax and correct values for the formulae.?

3. Look at INPUTBOX or MSGBOX with Yes and No options

4. If someone does it for you, then you have learnt nothing?

mike60smart · Jul 18, 2018

Hi Dave

Why not move the Formula from cell M7 and put it in I7

Redefine Table3 to include Column I

Then your code will copy down F, G ,H & I

david.paton · Jul 18, 2018

When I try and run it and press the add button row, this is the line that has the error:

Set tbl = ws.ListObjects("table3")

It says run time error 9, subscript out of range.
But that doesn't make sense as my table is set to table3??

I need some help with inserting and deleting rows in a table using vba in excel (1 Viewer)

Registered User.

Registered User.

Registered User.

Super Moderator

Registered User.

Super Moderator

Enthusiastic Amateur

Registered User.

Enthusiastic Amateur

Registered User.

Registered User.

Registered User.

Registered User.

Enthusiastic Amateur

Registered User.

Enthusiastic Amateur

Registered User.

Enthusiastic Amateur

Registered User.

Registered User.

Similar threads

Users who are viewing this thread