1

fairly straightforward question:

I've got an excel ListObject("list"). It's part of a longer procedure - in particular it is launched from an UserForm that triggers this procedure upon every Change

What I'm trying to do, is: Simply clear the table and leave only the .HeaderRowRange unchanged.

Dim temp as Range
Dim ws2 As Worksheet: Set ws2 = Sheets("Pomocne")
Dim tbl As ListObject: Set tbl = ws2.ListObjects("list")

If Not tbl.DataBodyRange Is Nothing Then
     tbl.DataBodyRange.ClearContents
     Set temp = tbl.HeaderRowRange
     Set temp = temp.Resize(2)
     tbl.Resize temp
End If

Now this works all nice and fine. However, weird part is, if I change the ComboBox too many times, then suddenly the Resize method fails for seemingly no reason.

enter image description here

enter image description here

Additionally, if I try to check anything, I get a message that Excel has run out of memory.

enter image description here

Is there perhaps any way, I could re-size the table in a more memory friendly way that would not result in an error after multiple changes? Or perhaps am I resizing the table range incorrectly?

Samuel Hulla
  • 6,617
  • 7
  • 36
  • 70
  • What is the value of `temp`? – Paul Ogilvie Mar 24 '19 at 12:59
  • @PaulOgilvie well it is a range, consisting of multiple cells, so `temp` as in within itself would return `Nothing`.. Upon the crash/error, the `temp.Address` returns `$B$2:$G$3` which is the correct range. – Samuel Hulla Mar 24 '19 at 13:41
  • Perhaps should have worded that comment better, not `Nothing`, but given it consists of multiple cells, it would nto be possible to print `.Value` – Samuel Hulla Mar 24 '19 at 13:52
  • There must be something else in your code, cause I couldn't replicate the error. Maybe check this answer for resizing/clearing a listboject: https://stackoverflow.com/questions/10220906/how-to-select-clear-table-contents-without-destroying-the-table – Ricardo Diaz Mar 24 '19 at 14:39

0 Answers0