Forum Discussion
Automatically insert formatted dropdown rows as per cell value
I very much need some help with my current project as my knowledge of excel is limited. I have been asked to create a spreadsheet for a survey. I have a dropdown table where the surveyor can ...
Many thanks for your response Martin, however when I open the file the formulas are missing?
Is there any way that you can type the formula in here please?
MT
Nicki
I did not use any formulas. This functionality is using VBA code (macros). To be able to see it, you just have to enable macros on this particular file. Once done, enable the "Developer" tab for the ribbon by going to File > Options > Customized Ribbon > Select Developer on the right.
Then, go to Developer > Macros and run the unique macro that it exists in the file. It automatically adds the rows based on the number value on cell B3.
If you prefer, I can share the code in here as well:
Sub AddRowsTotalAP()
Dim TotalAP As ListObject
Dim rowCount As Integer
Dim i As Integer
Set TotalAP = ThisWorkbook.Sheets("MySheet").ListObjects("TotalAP")
rowCount = ThisWorkbook.Sheets("MySheet").Range("B3").Value
For i = 1 To rowCount
TotalAP.ListRows.Add
TotalAP.DataBodyRange.Cells(TotalAP.ListRows.Count, 1).Value = "AP" & i
TotalAP.DataBodyRange.Cells(TotalAP.ListRows.Count, 2).Value = "Location"
TotalAP.DataBodyRange.Cells(TotalAP.ListRows.Count, 3).Value = "Cable"
TotalAP.DataBodyRange.Cells(TotalAP.ListRows.Count, 4).Value = "Metres"
TotalAP.DataBodyRange.Cells(TotalAP.ListRows.Count, 5).Value = ""
TotalAP.DataBodyRange.Cells(TotalAP.ListRows.Count, 6).Formula = "=INDIRECT(""R[0]C[-2]"",FALSE)*INDIRECT(""R[0]C[-1]"",FALSE)"
Next i
End Sub
Any further comments do not hesitate to reply!