Row Sequence number error

hsolanki

Board Regular
Joined
Jan 16, 2020
Messages
154
Office Version
  1. 2010
Platform
  1. Windows
Hi Everyone

i have an code where the it adds the seq number according to blank row number and it works fine however if i delete any selected rows from the listbox then all of the below sequence numbers becomes and invalid and shows as attached image #REF!

below it is the code to Add the sequence numbers which works perfect

VBA Code:
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Full")
Dim Last_Row As Long
Last_Row = Application.WorksheetFunction.CountA(sh.Range("A:A"))
sh.Range("A" & Last_Row + 2).Value = "=IF(B" & Last_Row + 1 & "="""","""",ROW()-2)"

below it is code to delete the selected row, whereby when i delete a row i get an error on the next row.
VBA Code:
Private Sub CommandButton3_Click()

If Me.ListBox1.ListIndex < 0 Then
    MsgBox "Please select a record to update", vbCritical
    Exit Sub
End If

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Full")

Dim Selected_Row As Long
Selected_Row = Application.WorksheetFunction.Match(Me.ListBox1.List(Me.ListBox1.ListIndex, 0), sh.Range("A:A"), 0)

sh.Range("A" & Selected_Row).EntireRow.Delete

End Sub
 

Attachments

  • seq no.jpg
    seq no.jpg
    13 KB · Views: 1

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,304
Office Version
  1. 365
Platform
  1. Windows
Perhaps:

sh.Range("A" & Last_Row + 2).Formula = "=IF(INDEX(B:B,ROW()-1)="""","""",ROW()-2)"
 
Solution

hsolanki

Board Regular
Joined
Jan 16, 2020
Messages
154
Office Version
  1. 2010
Platform
  1. Windows
Hi Stephen

it worked, Many thanks for all your help :)
 

Forum statistics

Threads
1,144,369
Messages
5,723,950
Members
422,527
Latest member
TotalBeginner201

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top