Styjun

Access to all elements on the page

Credit card offering 0.5 miles for every cent rounded up. Too good to be true?

Asking bank to reduce APR instead of increasing credit limit

Humans meet a distant alien species. How do they standardize? - Units of Measure

Applicants clearly not having the skills they advertise

Unorthodox way of solving Einstein field equations

How do I get a cleat that's stuck in a pedal, detached from the shoe, out?

Have powerful mythological heroes ever run away or been deeply afraid?

Accidentally cashed a check twice

How did rebel fighters get past the Scarif shield?

Is it possible for people to live in the eye of a permanent hypercane?

Is it a problem that pull requests are approved without any comments

How to decline physical affection from a child whose parents are pressuring them?

Opposite of "Squeaky wheel gets the grease"

Explain Ant-Man's "not it" scene from Avengers: Endgame

Hygienic footwear for prehensile feet?

Pros and cons of writing a book review?

Beginner's snake game using PyGame

Why use water tanks from a retired Space Shuttle?

How to detach yourself from a character you're going to kill?

Computing the differentials in the Adams spectral sequence

Is it legal in the UK for politicians to lie to the public for political gain?

Unconventional Opposites

GFCI Outlet in Bathroom, Lights not working

Excel keeps running out of memory on table.Resize - Method Resize of object ListObject failed

How to select clear table contents without destroying the table?Strange out of memory issue while loading an image to a Bitmap objectVBA to sort table and ignore total rowUserform combobox not populating on initializeExcel VBA: Run-time error (Method 'Value' of object 'Range' failed), but only on consecutive runsExcel VBA Error: 1004 Method 'Range' of object '_Worksheet' Failed when Selecting the Range of a Variables ValuePopulate data across monthly tabsShow only selected table column after filter to new worksheetsExcel Automation Error: Run-time error '-2147417848 (80010108)'excel VBA for resizing ranges refered with listobject rangesHow to prepolulate the combobox list from Excel into userform?

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

1

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.

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

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?

excel vba memory runtime-error out-of-memory

share|improve this question

edited Mar 24 at 12:36

Rawrplus

asked Mar 24 at 12:20

RawrplusRawrplus

3,55551436

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What is the value of temp?
  
  – Paul Ogilvie
  Mar 24 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.
  
  – Rawrplus
  Mar 24 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
  
  – Rawrplus
  Mar 24 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: stackoverflow.com/questions/10220906/…
  
  – Ricardo Diaz
  Mar 24 at 14:39
  

  
  
  
  

add a comment | 

1

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.

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

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?

excel vba memory runtime-error out-of-memory

share|improve this question

edited Mar 24 at 12:36

Rawrplus

asked Mar 24 at 12:20

RawrplusRawrplus

3,55551436

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What is the value of temp?
  
  – Paul Ogilvie
  Mar 24 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.
  
  – Rawrplus
  Mar 24 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
  
  – Rawrplus
  Mar 24 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: stackoverflow.com/questions/10220906/…
  
  – Ricardo Diaz
  Mar 24 at 14:39
  

  
  
  
  

add a comment | 

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.

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

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?

excel vba memory runtime-error out-of-memory

share|improve this question

edited Mar 24 at 12:36

Rawrplus

asked Mar 24 at 12:20

RawrplusRawrplus

3,55551436

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

share|improve this question

edited Mar 24 at 12:36

Rawrplus

asked Mar 24 at 12:20

RawrplusRawrplus

3,55551436

share|improve this question

edited Mar 24 at 12:36

Rawrplus

asked Mar 24 at 12:20

RawrplusRawrplus

3,55551436

asked Mar 24 at 12:20

RawrplusRawrplus

3,55551436

asked Mar 24 at 12:20

RawrplusRawrplus

3,55551436