VBA Find Last Row and Insert Empty Row Below but Keep Drop-Downs

[dd04518]

I have a spreadsheet that has multiple columns with drop-downs as well as data formatting for numbers. I need to have a macro that I can assign a button to that will find the last row with data and then insert an empty row beneath it but keep the drop-downs for the columns and the formatting of the data types in the cells. The sheet has 19 columns. I'm very new to VBA coding. I searched the forum for a thread but didn't find what I needed. Thank you.

 

[JLGWhiz]

Why would you insert a blank row where the row beneath the last row of data is already blank? To get the last row of a particular column, say column C, then

Dim nextEmptyRow As Long
nextEmptyRow = ActiveSheet.Cells(Row.Count, 3).End(xlUp)(2).Row

To get the last row of all the columns

Dim nextEmptyRowOnSheet As Long
nextEmptyRowOnSheet = ActiveSheet.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row

 

[dd04518]

The sheet is to track wounds monthly. Every month a new sheet will be created in the workbook using a button. The button will copy over the current sheet contents and then delete any "Healed" wounds from the new sheet. When I put formulas in for calculations I ran the formulas down to row 735 and so when the new sheet is copied over and then rows are deleted, I run out of rows that have drop-downs and formulas in them. I may have built it wrong??

 

[JLGWhiz]

If you change the parameter xlValues to xlFormulas as shown below, then this one would get the last row below your formulas and dropdowns.

Dim nextEmptyRowOnSheet As Long
nextEmptyRowOnSheet = ActiveSheet.Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row + 1

 

[dd04518]

Thank you!! Going to test it now!

 

[JLGWhiz]

But I don't know if that would cause a gap in your data of several rows. You might be better off sticking with the xlValues and then inser the rows you need.
this would allow you to insert rows by shifting rows with formulas with a value of "" down.

Sub t()
Dim rws As Long
rws = Application.InputBox("Enter the number of rows to insert.", "NUMBER OF ROWS")
ActiveSheet.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Offset(1).Resize(rws).EntireRow.Insert
End Sub

Test it on a copy of your file, not on the original.

 

[dd04518]

I didn't have success on this. Did I do it correctly as an activeX button?

 

[dd04518]

Is there a way to assign drop downs and formulas to columns dynamically? If there is, would that negate the need for the "Add New Row" button?

 

[dd04518]

Thank you again for your help. My apologies for being a newb.

 

[dd04518]

This code worked to create new rows with my drop downs but it didn't carry over my formulas like the one below.

Sub t()
Dim rws As Long
rws = Application.InputBox("Enter the number of rows to insert.", "NUMBER OF ROWS")
ActiveSheet.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Offset(1).Resize(rws).EntireRow.Insert
End Sub