VBA to Delete Partial Row (Selected by user)

[damianreese]

Hi Everyone, thanks for any help...

I created a financial calculator that helps people figure out which bills to pay off in which order to maximize snowball effect using Cash Flow Index. When someone wants to remove an debt entry, they click a button and enter the Debt ID (a digit 1-99). Currently it deletes the whole row, including some instructions on the right and some formulas that are throwing it all off.

I just want to delete cells A:L from the row of the Debt ID they enter. I've read several posts about how to select a range to delete and move up but there's something I'm not incorporating to make that work. There are a couple other commands that protect the sheet and fix the formulas after a change is made, so it's basically the 5th from the last line of code that is the full row delete.

Sub removeRow()

Dim toDelete As Variant
toDelete = vbNullString

numRows = findRange()
If numRows <= 2 Then
    MsgBox "You must keep at least two debts in the table."
    Exit Sub
End If

toDelete = InputBox("Which Debt ID do you want to delete?")

If toDelete = vbNullString Then
    Exit Sub
End If

If IsNumeric(toDelete) Then
    toDelete = CInt(toDelete)
Else
    MsgBox "Please enter an integer Debt ID value between 1 and " & numRows
    Exit Sub
End If

If toDelete < 1 Or toDelete > numRows Then
    MsgBox "Please enter an integer Debt ID value between 1 and " & numRows
    Exit Sub
End If

toDelete = toDelete + 7
Worksheets("DebtCalculator").Unprotect "password"
[COLOR=rgb(41, 105, 176)]Worksheets("DebtCalculator").Rows(toDelete).EntireRow.Delete[/COLOR]

Call fixFormulas
Call setCondFormat

Worksheets("DebtCalculator").Range("A8").Select
Worksheets("DebtCalculator").Protect "password"

Happy to share the whole spreadsheet if it needs a bigger tune-up.

Thanks,
Damian

 

[Fluff]

Hi & welcome to MrExcel.
How about

Worksheets("DebtCalculator").Range("A"& toDelete).Resize( ,12).Delete xlShiftUp

 

[Peter_SSs]

Welcome to the MrExcel board!

Just a forum posting hint. If you want to apply your own formatting to some vba code like you tried, use the 'rich' tags not the 'vba' tags.

The result:

toDelete = toDelete + 7
Worksheets("DebtCalculator").Unprotect "password"
Worksheets("DebtCalculator").Rows(toDelete).EntireRow.Delete

Call fixFormulas

 

[damianreese]

This works perfectly, thanks! I'll post a link to the final tool soon.

Hi & welcome to MrExcel.
How about

Worksheets("DebtCalculator").Range("A"& toDelete).Resize( ,12).Delete xlShiftUp

 

[Fluff]

You're welcome & thanks for the feedback.