Number of Selected Items in Listbox Determine Size of Table

[Dferrier]

Hello All Out There,

I have found myself at the all too familiar point of getting to last part of a VBA project and getting an issue that just wont go away.

What I want to do is have the user select some items in a List Box and then the code re-sizes a table to reflect the number selected. So that a nice chart can be created showing that data.

This is the offending code:

Dim i3 As Integer
Dim iD As Integer

i3 = Worksheets("Data").Range("K40").Row

            'iD is not picking up number of selected items, so iD = 7

Dim iL As Integer
Dim selCount As Integer
Dim iLC As Integer

iLC = Worksheets("Data").Range("O41").Row

   Do Until Worksheets("Data").Range("$O$" + iLC).Value = "" Or iLC = 200 'Error 13 Type mismatch error
   iLC = iLC + 1
   Loop
    
 iD = iLC - 1
    
MsgBox (iD) ' A temporary check to see if its picking up the right number

            'Error 1004: 'Range' of Object '_Global' Failed

Worksheets("Data").ListObjects("Table27").Resize Range("$O$40:$P$" & (i3 + iD))

I have searched left, right and centre, and high and low for a solution, but the ones I've tried just haven't quite done it.

The deadline for when this system is to be launched is looming.

See attached for the workbook if it helps.

Any Help will be greatly thanked.

Regards

[humdingaling]

try picking up the ID earlier in when you are checking the listbox

If bSelected = True Then
        With Worksheets("DATA").Range("Table27") 'Transfer to range
            '.Cells.Clear 'Clear transfer range
            For lItem = 0 To lRows
                If XListBox.selected(lItem) = True Then 'Row selected
                  'Increment variable for row transfer range
                  lTransferRow = lTransferRow + 1
                  iD = iD + 1
                    'Loop through columns of selected row
                    For lColLoop = 0 To lCols
                       'Transfer selected row to relevant row of transfer range
                       .Cells(lTransferRow, lColLoop + 1) = XListBox.List(lItem, lColLoop)
                        'Uncheck selected row
                        XListBox.selected(lItem) = False
                    Next lColLoop
                End If
            Next
        End With
         Unload Me
    Else ' NO listbox row chosen
         GoTo ErrorHandler
    End If

then use the full range

Worksheets("Data").ListObjects("Table27").Resize Worksheets("data").Range("$O$40:$P$" & (i3 + iD))

ps remember to declare ID at the start before it is required

[Dferrier]

Hello Humdingaling,

Your solution worked beautifully. Thank you.

Take some rep.

Regards