Delete Rows by comma separated row no using excel Vba

Question

Hi,

   two sheet namely entry sheet and Record sheet,  delete Entire Row in Record sheet using Row no in Entry sheet. row nos are  in  entry sheet at I7 cells, but that cells have comma separated row no,  whatever in I7 cells must delete Record sheet using  delete button in Entry sheet...

Given best solution in Vba, i am unble to found how to delete rows using comma separated rows no in excel other site...

i shared below file ... https://drive.google.com/file/d/1myg1E9i1zW-McEaJFh0PLz463HveGEZZ/view?usp=sharing

Thanks,

Answer 1

Hi,

Thanks for Reply.....

when execute code,, Error comes as run time Error "9", Subscript of out of Range

In the sample workbook, you gave us sheets are named Sheet1 and Sheet2 as shown in the picture below.

So if on the original workbook the sheets are named differently, you must adapt the code, and change the names of the sheets (highlighted in RED) accordingly.

So if Sheet1="Data Entry" and Sheet2="Records"

then

''''''******************************************************************

Sub DeleteMyRows()

Dim s As String

Dim RowArr() As String

Dim i As Long

s = Sheets("Data Entry").Range("I7").Value

''To split the comma separated rows

RowArr = Split(s, ",")

Application.ScreenUpdating = False

''' To delete rows we need to do it from the bottom since they shift up.

For i = UBound(RowArr) To LBound(RowArr) Step -1

Sheets("Records").Range("A" & RowArr(i)).EntireRow.Delete

Next i

Application.ScreenUpdating = True

MsgBox "Job done"

End Sub

'''''*********************************************************

Notes:

If you want to use the command button to execute the macro then

Use the code

Private Sub CommandButton1_Click()

    Call DeleteMyRows 

End Sub

OR

Private Sub CommandButton1_Click()

Dim s As String

Dim RowArr() As String

Dim i As Long

s = Sheets("Data Entry").Range("I7").Value

''To split the comma separated rows

RowArr = Split(s, ",")

Application.ScreenUpdating = False

''' To delete rows we need to do it from the bottom since they shift up.

For i = UBound(RowArr) To LBound(RowArr) Step -1

Sheets("Records").Range("A" & RowArr(i)).EntireRow.Delete

Next i

Application.ScreenUpdating = True

MsgBox "Job done"

End Sub

Anyways

Here is the link to your file with the code and answer to your question

https://we.tl/t-oxKCaQsOmh

Regards

Jeovany

Answer 2

Hello Arunsharma_955

I expect the error message is due to the tab names in your workbook not being the same as the sample code.

Please modify the code to Sheets("Sheet1") to read Sheets("thesheetname") and also lower down Sheets("Sheet2") please do the same change.

I tried it both ways and when I changed the name sheets to Entry and Record without changing the VB code it generated the error above.

The new code should look like this, depending on your sheet names.

Sub DeleteMyRows()

Dim s As String

Dim RowArr() As String

Dim i As Long

s = **Sheets("Entry").**Range("I7").Value

''To split the comma separated rows

RowArr = Split(s, ",")

Application.ScreenUpdating = False

''' To delete rows we need to do it from the bottom since they shift up.

For i = UBound(RowArr) To LBound(RowArr) Step -1

       **Sheets("Record").R**ange("A" & RowArr(i)).EntireRow.Delete 

Next i

Application.ScreenUpdating = True

MsgBox "Job done"

End Sub

I hope this helps.

CDN-Carl

Answer 3

Hi,

Thanks for Reply.....

 when execute code,, Error comes   as   run time Error "9", Subscript of out of Range

Answer 4

Hi Arunsharma

I tested this code on your sample file and works fine.

'''''********************************************************************

Sub DeleteMyRows()

Dim s As String

Dim RowArr() As String

Dim i As Long

s = Sheets("Sheet1").Range("I7").Value

''To split the comma separated rows

RowArr = Split(s, ",")

Application.ScreenUpdating = False

''' To delete rows we need to do it from the bottom since they shift up.

For i = UBound(RowArr) To LBound(RowArr) Step -1

       Sheets("Sheet2").Range("A" & RowArr(i)).EntireRow.Delete 

Next i

Application.ScreenUpdating = True

MsgBox "Job done"

End Sub

''''***************************************************************

I hope this helps you and gives a solution to your problem

Do let me know if you need more help

Regards

Jeovany