I need some help with inserting and deleting rows in a table using vba in excel

D

david.paton

Registered User.
Local time
Yesterday, 21:40
Joined
Jun 26, 2013
Messages
338
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
 
Hi Dave

I would create a macro to carry out what you need and apply the Macro to a Command Button.
 
That's what I have done but I am not sure of the code.
 
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.
Click to expand...
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
 
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?
 
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.
Click to expand...
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.
 
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?
Click to expand...
 
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.?
Click to expand...


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?
 
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
 
I am going to try instead of the table, converting it to a range and see how I go.
 
I am feeling a little confused. Should I leave it as a table or change it to a range?
 
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
Click to expand...


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.
 
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.
 
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
 
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?
 
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")
 
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))



  1. I have it all in the one table now. F, G and H fill down when I create a row but M doesn't.
  2. 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.
  3. In addition, I also wanted it to prompt the user when deleting a row, if there is text in that row.
  4. 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!


:)
 
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?
 
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
 
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??
 
You must log in or register to reply here.

Similar threads

R
Updating Excel spreadsheet from within the Access
Replies
8
Views
1,299
CJ_London
C
H
How to insert some data from Excel spreadsheet and the other data from Access combo boxes
Replies
6
Views
471
Pat Hartman
P
B
Set a pie chart with values from a listbox access vba
Replies
3
Views
275
b24 sofwtares
B
R
Closing Excel from Access VBA
2 3
Replies
59
Views
2,477
ebs17
E
L
Consuming an API to retrieve data and store it in a Microsoft Access table.
Replies
3
Views
689
lbcurrier
L

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom