VBA Insert Rows Below Instead of Above

Thank you "peiyezhu",

This fix worked perfectly.

However, the only problem now is figuring out how to format the newly added rows so that only certain cells are formatted. Now when I insert a new row there is a thin bottom border that is selected for the entire row (not a specific range). You can see what I mean in the image below. In rows 30-32, only certain columns have the thin bottom border (columns: D, F, H, J, L, N, O, P, Q, R, S, T, U, V, X). Is there a solution to fix this?

I appreciate everyone's assistance in solving my issues.

Thank you.

Please, try replacing

. Cells(4). Formula = "=LEFT(D" & lastRow + 1 & ",3)&(VALUE(MID(D" & lastRow + 1 & ",4,4))+1)&RIGHT(D" & lastRow + 1 & ",1)"

With

. .Cells(4). Formula = "=LEFT(D" & lastRow & ",3)&(VALUE(MID(D" & lastRow  & ",4,4))+1)&RIGHT(D" & lastRow & ",1)"

Hi there

It seems that a double quote is missing before column letter D in the inserting the formula part code

Please, try replacing

. Cells(4). Formula = "=LEFT(D" & lastRow + 1 & ",3)&(VALUE(MID(D" & lastRow + 1 & ",4,4))+1)&RIGHT(D" & lastRow + 1 & ",1)"

With

. Cells(4). Formula = "=LEFT("D" & lastRow + 1 & ",3)&(VALUE(MID("D" & lastRow + 1 & ",4,4))+1)&RIGHT("D" & lastRow + 1 & ",1)"

Regards

Jeovany

Hi Shakiru,

Thank you very much for your quick response. I did try your code. While it did insert another row on the bottom, it did not format the way I wanted it to. The formula in column "D" did not properly work either. Do you recommend another alternative? I took a couple of screenshots of what my spreadsheet looks like before and after I used your code if that helps.

Before using the code (before clicking the blue "Insert Row" button):

After using the code (after clicking the blue "Insert Row" button):

Thank you so very much for your help!

Best Regards.

Hi Meagan S!

I am Shakiru, an independent advisor and a user like you, and I am glad to be helping you out today.

Please try this:

Private Sub CommandButton1_Click() Dim lastRow As Long Dim ws As Worksheet

' Set the worksheet object Set ws = ThisWorkbook.Sheets("Spend")

' Find the last used row in column D lastRow = ws. Cells(ws. Rows.Count, "D"). End(xlUp). Row

' Insert a new row below the last used row ws. Rows(lastRow + 1). Insert Shift:=xlDown

' Apply formatting and formulas to the new row With ws. Rows(lastRow + 1) . Borders(xlEdgeBottom). Weight = xlThin . Cells(4). Formula = "=LEFT(D" & lastRow + 1 & ",3)&(VALUE(MID(D" & lastRow + 1 & ",4,4))+1)&RIGHT(D" & lastRow + 1 & ",1)" ' Add additional formatting as needed for other columns End With End Sub

This code will find the last used row in column D, then insert a new row below it and apply formatting and formulas to the new row accordingly.

Note: Make sure to replace "Spend" with the actual name of your worksheet if it's different.

Please let me know how it goes and if you need any other assistance and I will be more than happy to help further.

Thank you

Best Regards, Shakiru