How to clear an Excel array from vba?

Question

How to clear an Excel array ( with unknown shape) from VBA ? Given that I know that the array commences from "A1" for example. So I need to get the shape , select and clear.

Do you mean a Range of cells or an internal VBA array ?? — Gary's Student
– Gary's Student, Commented Mar 20, 2014 at 10:55
Not internal VBA array, but Excel array ( the one that displays "you cannot change part of an array" when you try to delete one single cell instead of the whole array ). Does this make sens ? — DKK
– DKK, Commented Mar 20, 2014 at 11:03
ANd how would I know what is the shape of "MyRange" ? My problem is that I don't know how to select it in order to clear its contents — DKK
– DKK, Commented Mar 20, 2014 at 11:09

Gary's Student · Accepted Answer · 2014-03-20 11:36:18Z

2

If you mean a range of cells:

MyRange.Clear

or

MyRange.ClearContents

EDIT#1

To clear a range of cells requires two parts:

Set the Range
apply a Clear method

If you have a block of formulas starting in A1 then your posted code will work in a fashion similar to:

Sub ClearCells()
    Dim RangeToClear As Range
    '
    'the first part
    '
    d1 = Range("A1").End(xlToRight).Column
    d2 = Range("A1").End(xlDown).Row
    Set RangeToClear = ActiveSheet.Range(Cells(1, 1), Cells(d1, d2))
    '
    'the second part
    '
    RangeToClear.ClearContents
End Sub

will work............I meant the the second part requires only one statement.

edited Mar 20, 2014 at 11:36

answered Mar 20, 2014 at 11:01

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

DKK Over a year ago

Common I wouldnt have asked such a simple thing, I have an excel array formula with unknown shape !

Gary's Student Over a year ago

It does not matter what the shape is!! You can clear all the cells at the same time with one command! You don't have to loop over rows or columns!

DKK Over a year ago

one command ? how would vba know what cells to delete from the excel sheet ? it is not very clear what MyRange is in your answer.

DKK Over a year ago

Ya my problem was the the first part, I was wondering if there was a better way to do this.

Gary's Student Over a year ago

Now I understand....if the cells are part of an array formula block then use Range("A1").CurrentArray.ClearContents

DKK · Accepted Answer · 2014-03-20 11:16:13Z

0

A quick and dirty solution is to use :

d1 =Range("A1").End(xlToRight).Column 
d2 =Range("A1").End(xlDown).row 
ActiveSheet.Range(Cells(1, 1), Cells(LastRowData, LastColData)).ClearContents

answered Mar 20, 2014 at 11:16

DKK

1,9304 gold badges16 silver badges22 bronze badges

Collectives™ on Stack Overflow

How to clear an Excel array from vba?

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related