Delete selected range in listbox and the corresponding Range in worksheet and shift cells up

Hi Everyone......Due to the current lock down I am having plenty of time modifying my workbook.....

The following code is supposed to delete the selected Rows in the userform listbox from column "B" and Column "E" in the range ie B4:B14 and E4:E14 and shift the remaining cells up.

Column "B" works fine ....problem is with Column "E" the last row in this column gets deleted instead of the selected row

and please note there other tables and data beyond and below row number 14......so all deleting and shifting up should be done in this range only ie B4:B14 and E4:E14

If you run the attached workbook sample with the codes the problem will be noticed.

To modify this code is beyond my understanding......

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
          
            Call subDeleteAndMoveUp(ActiveSheet.Range("B" & i + 4))
          
            Call subDeleteAndMoveUp(ActiveSheet.Range("E" & i + 4))

         End If
        
    Next i
 
End Sub



Public Sub subDeleteAndMoveUp(rngToDelete As Range)
Dim rng As Range
Dim intlastRow As Integer
Dim i As Integer

    intlastRow = 14
  
    For i = 1 To 11
        If IsEmpty(Cells(i + 3, rngToDelete.Column)) Then
            intlastRow = i + 3 - 1
            Exit For
        End If
    Next i
        
    Set rng = Cells(4, rngToDelete.Column).Resize(intlastRow - 3, 1)
    
    If rngToDelete.Row = intlastRow Then
            
        rngToDelete.Value = ""
        
    Else
        
        Set rng = Cells(4, rngToDelete.Column).Resize(intlastRow - 3, 1).Range(rng.Cells(rngToDelete.Row - 5, 0), rng.Cells(8, 0))
                        
        rng.Offset(-1, 0).Value = rng.Value
        
        Cells(intlastRow, rngToDelete.Column).Value = ""

    End If

End Sub

Hi Carim.....

Please note carefully ......that is what i overlooked.....

Say we select row 4 on the listbox and press delete......

what happens is the column "B" value is perfectly selected, deleted and the remaining rows shift up..

But in column "E" the last cell in this column is deleted and not the selected row cell .

No..Carim, I can't delete the entire row and as a matter of fact any rows below row 14...

only this specific range has to be deleted and remaining cell below it shifted up..

This is due to other tables and data present on the right and below this range.....

Carim....I understand.....but this is not possible at this stage.

please find a solution to this problem with the current code and the current situation.

Tried you code.......column B and Column E are getting deleted perfectly and the shift up is perfect, but also column A,C and D....are being deleted, these columns have formula and get updated automatically. Also The values are not deleting from listbox

Yes Carim.....all columns in range B4:F14 have different functions...

I have a userform from where a product Barcode is scanned.....these barcodes are added in Range "B4:B14

Range C4:C14 has VlookUp formula wherein the product names are populated.

Range D4:D14 has VlookUp formula and populates the unit Price

Range E4:E14 is the quantity input column..default "1"

and F4:F14 is the Total ie Unit Price X Qty.

so far say if a range of products were scanned. i could not remove any item from the list....I had to cancel the whole transaction and re-scan again.

This is the reason this is necessary. Only the selected cells of B4:F14 and E4:E14 have to be removed and the rest of the range takes care of itself.

Hope I have explained to your satisfaction.

Thanks for your time...

Thanks Carim.....Have downloaded your test file...just give some time....i'll have to check the functioning in the original workbook.

will revert back soon

Hi Carim.....your code is working perfect with the sample sheet.....but is creating havoc when i place it in my worksheet

This might be due to the difference in the initialization of the userform

My rowsource is defined (frmSaleScan.ListBox1.RowSource = "SaleScan") in the initialization code....... where saleScan= Range("B4:F17")

Private Sub UserForm_Initialize()

Sheet1.Activate
Sheet1.Range("C2").Select

frmSaleScan.ListBox1.RowSource = "SaleScan"


frmSaleScan.TextBox2.Value = ""
frmSaleScan.TextBox4.Value = ""
frmSaleScan.TextBox6.Value = Sheet1.Range("G23").Value
frmSaleScan.TextBox3.Value = Sheet1.Range("D1").Value
frmSaleScan.CheckBox1.Value = False
frmSaleScan.TextBox2.Enabled = False
frmSaleScan.TextBox4.Enabled = False
frmSaleScan.TextBox7.Enabled = False
frmSaleScan.CommandButton8.Enabled = False


If Sheet1.Range("B4").Value = "" Then
frmSaleScan.ListBox1.Enabled = False
End If

If Sheet1.Range("C4") <> "" Then
frmSaleScan.TextBox2.Enabled = True
End If

frmSaleScan.Label8.Caption = Sheet1.Range("C1").Value
frmSaleScan.Label12.Caption = Sheet1.Range("B1").Value
frmSaleScan.Label13.Caption = Sheet1.Range("F1").Value
frmSaleScan.Label11.Caption = Sheet1.Range("E1").Value
Sheet1.Range("D21").Value = Sheet1.Range("D19").Value - Sheet1.Range("D20").Value

With Application
.WindowState = xlMaximized
Zoom = Int(.Width / Me.Width * 100)
Width = .Width
Height = .Height
End With
    
Me.Height = Me.Height - 10
HideTitleBar.HideTitleBar Me


End Sub

The other list box properties have been defined in the Listbox property window.....

One important thing to note is the Column heads are set to true....

I have retained my initialization code......

now the delete code you have provided is giving the following error on this line of code

If ListBox1.ListIndex >= 0 Then

ListBox1.RemoveItem ListBox1.ListIndex

I am sure it has to to something with the list index value......

Please look into the matter and i am sure this is going to workout

Thanks Carim