Delete cell in worksheet based on selected Row in Listbox

[zubin]

Hi everyone....
I Have a userform with a listbox which is populated with row source property.

After selecting a row in the Userform, A delete Key should remove the selected Row in the list box and the corresponding worksheet cell values in the range "B4 to B14" and "E4 to E14"
and cells values below (If at all present) these should shift up...... and NOT the whole column.

I am so far able to delete only values from one column ie "B" and that too the whole column beyond "B14" shifts up, this should not happen as there is another table below this range.

Columns "C", "D" and "F" in the worksheet Range B4:F17 have formulas and are populated as per the values of Column "B" and "E".
Currently I am using this code.....

Please Help....

Private Sub CommandButton1_Click()
Unload Me
End Sub

Private Sub CommandButton2_Click()

    
    Dim i As Long
        
    For i = 0 To Me.ListBox1.ListCount - 1
            
        If Me.ListBox1.Selected(i) = True Then
          
           Range("B" & i + 4).Delete Shift:=xlUp
          
         End If
        
    Next i

End Sub

Book1.xlsm
	A	B	C	D	E	F	G	H	I
1
2
3	S.No.	BarCode	Item	Unit Price	Qty.	Total
4		2			8
5		3			3
6		2			5
7		5			4
8		5			3
9		3			2
10		2			1
11		3			2
12		8			9
13		7			8
14
15
16
17
18
19
20		58			2
21		8			7
22		7			8
23		8			9
24		9			7
25		6			8
26		1			5
27		8			2
28
29
30
31
Sheet1

 

[Domenic]

Unclear... To delete the entire row, try...

Range("B" & i + 4).EntireRow.Delete

Hope this helps!

 

[zubin]

Unclear... To delete the entire row, try...

Range("B" & i + 4).EntireRow.Delete

Hope this helps!

No...can't be done ...only Values from column "B" and Column "E" should delete.
.Entire row will delete a lot of other data

 

[Domenic]

In that case, can you please clarify?

 

[Herakles]

Using this procedure you will be able to effectively delete a cell value and move the cells values below this cell, down until the first blank cell, up by one row.

Call the procedure using a line such as this using the single argument, the cell range to be deleted, as appropriate.

Call subDeleteAndMoveUp(ActiveSheet.Range("B5"))

Public Sub subDeleteAndMoveUp(rngToDelete As Range)
Dim rng As Range

    ' This next line is not really required as this cell value
    ' will be replaced with that from the cell below.
    rngToDelete.Value = ""
    
    Set rng = rngToDelete.Offset(1, 0).Resize(rngToDelete.Offset(1, 0).End(xlDown).Row - rngToDelete.Row, 1)
    
    rng.Offset(-1, 0).Value = rng.Value
    
    rng.Cells(rng.Rows.Count, 1).Value = ""
    
End Sub

 

[zubin]

Thanks all..... for the prompt reply,

Herakles Ill try out you code...but exactly how to fit in my current codes for the delete button ?

Domenic, I am not too familiar with vba, and still a learner. sorry if I am not able to put my problem, I hope you will understand.

Now, is there a way I can put up my sample worksheet with the codes, so as you can understand what i exactly need.

However let me explain... As you can see in the above worksheet....(This is a part of a inventory workbook.)
Column A ie A4:A14 s.no. of items scanned (Barcode)
Column B ie B4:B14 scanned barcode numbers are displayed...total 11 items can be scanned
Column C ie C4:C14 of the worksheet has Vlook up formula to search for the Item Names
Column D ie D4:D14 also has Vlook up formula for the item price
Column E ie E4:E14 is Quantity and last
Column F ie F4:F14 is the total Amount

The range A4:F14 is displayed in the listbox ......The headers are in the 3rd row ie A3:F3
Say I have scanned 7 items ......so seven items are displayed in the list box with their respective names, price and so on..
If by any chance i need to remove an item from the scanned list....i should be able to select the item in the listbox and delete the row from the listbox as well as the corresponding row in the worksheet.
The Delete button should only remove the value from the column "B" ie the scanned bar code and column "E" ie Quantity of the selected item.
and shift the remaining rows below one cell up.....remember column "C", "D" and "F" have formulas which will update by them self.

So basically only the Range B4:B14 and E4:E14 have to delete and the row shifting should happen only in this range and not the rows below
the 14 row as there is another table.

I hope I have explained the problem well.

 

[Herakles]

This is what the procedure that I posted will do.

If you want to delete the value in cell B5 for example, and then move the values below up, then put this line of code into the code behind the
delete button.

Call subDeleteAndMoveUp(ActiveSheet.Range("C" & intRow))

The intRow variable needs to hold the row number.

When the Delete button is selected how do you ascertain the appropriate row number.?

Is the worksheet that you need to delete from the active sheet?

Remember, you are not shifting rows. You are just moving data from one range to another under strict control.

 

[zubin]

Hi Herakles...
I have a userform with a listbox which is populated by the listbox Rowsource property. And yes the worksheet is the active worksheet.

 

[Herakles]

So.... when you click on the delete button how do you get the row number from the selected item in the listbox?

Is the listbox only used for the selection of which row to delete?

If so then why not dispense with the Delete button and ask the user for confirmation when they click on the listbox?

Once the sheet data has been updated I would repopulate the listbox.

 

[Herakles]

This is what the procedure that I posted will do.

If you want to delete the value in cell B5 for example, and then move the values below up, then put this line of code into the code behind the
delete button.

Call subDeleteAndMoveUp(ActiveSheet.Range("C" & intRow))

The intRow variable needs to hold the row number.

When the Delete button is selected how do you ascertain the appropriate row number.?

Is the worksheet that you need to delete from the active sheet?

Remember, you are not shifting rows. You are just moving data from one range to another under strict control.

Correction :

Call subDeleteAndMoveUp(ActiveSheet.Range("B" & intRow))