Solved: Use multiselect listbox values to delete specific table rows

[robbykube]

Hello guys,
It's probably an easy fix but I've been on it for some time and don't know how to solve this problem.

I have a table in my word document. I want things to be set up so that when I select one item from the multi-select listbox I have , this item is linked to a single row, and the other rows are either deleted or hidden. If I select two of the items, all rows but those related to those two items are deleted etc. Basically I want each value in the list box to be linked to a row in the table such that if I slect it, its row is not deleted, but all other rows are.

Could you help me out with this? I've been trying for a while but don't know where to start... probably the lack of experience.

Thanks.

[fumei]

You say you have been trying? OK, post what you have tried so far.

[robbykube]

I've been through many pieces of code, trying to mix and match to see if it works(Problem is I never took any basic programming classes). They were pretty lame attempts. The last thing I've tried is bookmarking specific rows. Now if I can find a way to link a listbox item to specifc bookmarks, then I can select the whole bookmark and delete it, thereby erasing the row. It will be cumbersome but that is the only thing I can think of for now. What I know how to do is the delete part. I played witha piece of code. This one:

Dim x As Integer
' Loop through all items in the ListBox.
For x = 0 To Me.lstbioch.ListCount - 1

' Determine if the item is selected.
If lstbioch.Selected(x) = True Then
' Deselect all items that are selected.
lstbioch.Selected(x) = False
Else
' Select all items that are not selected.
lstbioch.Selected(x) = True
End If
Next x

That way if I choose two items in my list box(ie the items I want, all other items are selected). If I can link all the items to bookmarks, I think I should be done... hopefully.

[fumei]

"Now if I can find a way to link a listbox item to specifc bookmarks, then I can select the whole bookmark and delete it, thereby erasing the row."

Oh no it doesn't. Deleting the bookmark (or its range) would NOT delete the row. Have you actually tried?

Oh...or are you talking about deleting the contents of the row - the text - but not the row itself?

Please clarify.

[robbykube]

Actually when I delete the contents of the whole row, it deletes the row. But whatever ideas you have are welcome. What I want to do is delete the actual row, not just the contents.

[fumei]

"Actually when I delete the contents of the whole row, it deletes the row"

Please describe this. When I delete contents, the row is not deleted. If i bookmark a row and delete the bookmark (and thus the contents) the row is NOT deleted.

[robbykube]

Oh scratch that I don't use delete to do that usually. I use the backspace. Then either it deletes the row or the program asks me if I wish to delete the row and i press enter.

[fumei]

Ummm, what version are you using? Because if I backspace in the contents of a cell Word does NOT delete a row, NOR do I get asked if i want to delete a row.

[robbykube]

I'm using 2013. I said the contents of the row though. Not the contents of a single cell. I select all in the row and then click backspace. At least it's always worked for me.

[robbykube]

but do you have other ideas? Maybe another method?

[gmaxey]

I'm Johnny come late in the thread, but it seems something like this should work:

Sub ScratchMacro()
'A basic Word macro coded by Greg Maxey
Dim oTbl As Word.Table
Dim arrNum() As String
Dim lngIndex As Long
'The next line represents the table rows that you have selected to delete
'i.e., rows 1, 3 and 5.
arrNum = Split("1,3,5", ",")
Set oTbl = Selection.Tables(1)
'Delete in reverse order.
For lngIndex = UBound(arrNum) To 0 Step -1
oTbl.Rows(arrNum(lngIndex)).Delete
Next lngIndex
End Sub

[robbykube]

Hi
Oh yes I've been to your website. Lots of interesting stuff there. It helped me on some other portions of the project.
It looks like I could use this too.
So, the code here deletes the rows I choose right? Then what can I do to link my listbox selection to each row?

Thanks for the help.

[gmaxey]

You have already posted code to determine which listbox items you selected. Use that code to populate arrNum in the code I provided.

[robbykube]

Alright, I'll play with that and see what I get. Thanks.

Rob.

[fumei]

Something like:
Private Sub CommandButton1_Click()
Dim DeleteRow()
Dim var
Dim i As Long
For var = ListBox1.ListCount - 1 To 0 Step -1
If ListBox1.Selected(var) = False Then
ReDim Preserve DeleteRow(i)
DeleteRow(i) = var + 1
i = i + 1
End If
Next
For var = 0 To UBound(DeleteRow())
ActiveDocument.Tables(1).Range.Rows(DeleteRow(var)).Delete
Next
Unload Me
End SubThe code goes through the listbox, and if an item is NOT selected - ListBox1.Selected(var) = False - adds that number to the array.

So if you SELECT (out of five items) 1 and 4, then 2, 3 and 5 are put into the array.

Then those numbers (equaling rows) are deleted. 1 and 4 (those selected) remain.

Obviously, you need to have the code apply to the correct table. Greg used the table the Selection is in; it most certainly does not have to be that one. It can be ANY table. I am assuming your listbox is on a userfrom, since you used Me.

[robbykube]

Thanks for this. I'll try it out.

Yes, the list box is on a userform.
I guess this means I don't need the piece of code I posted earlier any more. This seems simpler to implement too.
By the way, what does ReDim Preserve do?

Thanks again.

[robbykube]

Wow. That worked. I just had to insert the lists name and it deleted rows. Now I just need to check my table's settings and the code because it seems it's unable to delete some rows in my table. Maybe it's because some cells are merged in some areas. I might have to get rid of that to make things easier.

It seems I have to check the table settings to make it work optimally.

Just one last thing. Could you please explain how the code works? I get the general gist of it but I'm unsure what is happening exacty. For example, why:

DeleteRow(i) = var + 1
i = i + 1

I'm probably missing something, but this seems redundant to me.

And why Dim DeleteRow in the beginning?

Finally what is the role of UBound?

Sorry, hope I'm not being too much of a bother. I'd just like to know. Who knows it could be helpful later on.

[gmaxey]

Maybe different variable names will help:

Option Explicit
Dim lngIndex As Long 'Declare a counter
Private Sub CommandButton1_Click()
Dim arrRowsToDeleteByIndex() As Long 'Declare a dynamic array
Dim lngAllocatedSpaces As Long
'Load array in reverse order last row number to first row number.
For lngIndex = ListBox1.ListCount To 1 Step -1
If ListBox1.Selected(lngIndex) = False Then
'Dimension the array. You will start with 0
ReDim Preserve arrRowsToDeleteByIndex(lngAllocatedSpaces)
'Arrays are indexed from 0. So the first position that holds data is 0
arrRowsToDeleteByIndex(lngAllocatedSpaces) = lngIndex
'Uptick
lngAllocatedSpaces = lngAllocatedSpaces + 1
End If
Next
'Highlight UBound in the VBE and press F1
Debug.Print UBound(arrRowsToDeleteByIndex)

For lngIndex = 0 To UBound(arrRowsToDeleteByIndex())
ActiveDocument.Tables(1).Range.Rows(arrRowsToDeleteByIndex(lngIndex)).Delet e
Next
Unload Me
End Sub
Private Sub UserForm_Initialize()
For lngIndex = 1 To ActiveDocument.Tables(1).Rows.Count
Me.ListBox1.AddItem lngIndex
Next lngIndex
End Sub

[fumei]

Hi robby.

"Maybe it's because some cells are merged in some areas."

Yes. Merged cells do not work well (if at all) in VBA. They will likely mess things up.

"And why Dim DeleteRow in the beginning?"

Because I use (as does Greg...as should you) Option Explicit. You need to declare variables in order to use them.

There are different ways to do things, but essentially the issue with the + 1 stuff is that arrays are (unless otherwise defined) 0-based. Rows in tables do not have a 0 row; they start at 1. So you have to make sure they match, one way or the other.

[robbykube]

Thanks for the code Greg, I'll go through it to see how I can use it.

[robbykube]

Hi robby.

"Maybe it's because some cells are merged in some areas."

Yes. Merged cells do not work well (if at all) in VBA. They will likely mess things up.

"And why Dim DeleteRow in the beginning?"

Because I use (as does Greg...as should you) Option Explicit. You need to declare variables in order to use them.

There are different ways to do things, but essentially the issue with the + 1 stuff is that arrays are (unless otherwise defined) 0-based. Rows in tables do not have a 0 row; they start at 1. So you have to make sure they match, one way or the other.

Thanks for the explanation. I do need to check to always make sure I use option explicit. I'm not yet used to it.

And for the merged cell, I was able to work around that.

"There are different ways to do things, but essentially the issue with the + 1 stuff is that arrays are (unless otherwise defined) 0-based. Rows in tables do not have a 0 row; they start at 1. So you have to make sure they match, one way or the other"

Okay. I get it. That's the kind of stuff I woulld have skipped completely.

Thanks again.

[fumei]

"I do need to check to always make sure I use option explicit."

In the VBE, go Tools > Options and check Require Variable Declaration. This puts Option Explicit autyomatically into any NEW modules. Stanard modules, class modules, userform modules. It does NOT put Option Explicit into any existing modules.

Note that when you have Require Variables Declaration (and thus Option Explicit) you get IntelliSense.

Intellisense means that when you type a known object the VBA editor will put up a list of all available properties and methods. You can also get this massive list by pressing Ctrl-Spacebar. Using this can speed up entering VBA terms a LOT.

[robbykube]

Thanks. I'll definitely do that.