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......

  • Hello,


    Just tested your UserForm macro and everything seems to be working as expected ...:)


    Even independently ... both Columns B and E are handled properly ....

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • 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 .

  • Hello,


    Changed the quantities to 10,20,30, etc ... to visually get something different than your Serial Numbers ...


    And now ... understand your question ...:)


    Why don't you just delete the entire row ... ? Is there a specific reason ...?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • 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.....

  • Can easily understand you have other tables below ...


    BUT to the right , they should be moved to another tab ... !!!!


    Otherwise, you will create yourself huge headaches ...:thumbdown::cursing:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • My suggestion for your Delete button would be :


    Code
    1. Private Sub CommandButton2_Click()
    2. Dim i As Long
    3. For i = 0 To Me.ListBox1.ListCount - 1
    4. If Me.ListBox1.Selected(i) = True Then
    5. Sheet1.Rows("14:14").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    6. Sheet1.Rows(i + 4 & ":" & i + 4).Delete Shift:=xlUp
    7. End If
    8. Next i
    9. End Sub


    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • In your real-life spreadsheet :


    1. What kind of coherence has to be preserved for your A4:F14 range ...???


    2. Is each Column entirely independent from the others, apparently Column B and Column E are 'twins' ...

    so do you have other 'twins' ...???

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • 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

  • Understand your constraints ...


    Please ... can you go back to my message # 9 ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • 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.

  • OK ... thanks for your explanation ...


    Need to test the link between Column B and E ...;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • So far, the only patch I can think about would be to concatenate Column B and Column E ... say in Column G or H ...


    Is that feasible ... in real life ...???;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Currently ... for you own test ... the UserForm code would be as follows :



    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hello again,


    Attached is your test file ... without any inelegant patch ...


    Had never been confronted with a similar request ...:/


    Thanks for this opportunity ...:)


    Hope this will help

    :)

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • 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")

    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


    Code
    1. If ListBox1.ListIndex >= 0 Then
    2. 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

  • Glad to hear macro is working fine with your first sample file :)


    Regarding the problems you are facing for the transposition in your real-life workbook ...


    The best thing to do would be to attach a second sample file much closer to the reality than the one you have posted ...;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)