I have a sheet of data that I replace by importing data from another
workbook, however I need to clear out the original sheet since the data might
not use the same amount of rows.

My workbook is 18mb.

I can have 22000 rows to delete from column A to AE.

I have tried the following, all work, however they take an age to do:

ThisWorkbook.Sheets(data_type & " Data").Cells.ClearContents
or
ThisWorkbook.Sheets(data_type & " Data").Range("A1:AE22000").ClearContents
Note: This is a test and if it was quick then I would need to work out how
to change ("A1:AE22000") to use r instead of 22000 from below
r = .Range("A1").CurrentRegion.Rows.Count

Is there anyway of doing this quickly, i.e. something that take less than a
minute. Mine takes several minutes.

try to set up the calculation to manual before deleting
and then reset it to automatic when finished
With Application
.Calculation = xlManual
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False


With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False


--
caroline


"Paul Dennis" wrote:

> I have a sheet of data that I replace by importing data from another
> workbook, however I need to clear out the original sheet since the data might
> not use the same amount of rows.
>
> My workbook is 18mb.
>
> I can have 22000 rows to delete from column A to AE.
>
> I have tried the following, all work, however they take an age to do:
>
> ThisWorkbook.Sheets(data_type & " Data").Cells.ClearContents
> or
> ThisWorkbook.Sheets(data_type & " Data").Range("A1:AE22000").ClearContents
> Note: This is a test and if it was quick then I would need to work out how
> to change ("A1:AE22000") to use r instead of 22000 from below
> r = .Range("A1").CurrentRegion.Rows.Count
>
> Is there anyway of doing this quickly, i.e. something that take less than a
> minute. Mine takes several minutes.
>
>

I have all calulations already turned off in my workbook and then force an
application recalc once all the new data is in.

"caroline" wrote:

> try to set up the calculation to manual before deleting
> and then reset it to automatic when finished
> With Application
> .Calculation = xlManual
> .MaxChange = 0.001
> End With
> ActiveWorkbook.PrecisionAsDisplayed = False
>
>
> With Application
> .Calculation = xlAutomatic
> .MaxChange = 0.001
> End With
> ActiveWorkbook.PrecisionAsDisplayed = False
>
>
> --
> caroline
>
>
> "Paul Dennis" wrote:
>
> > I have a sheet of data that I replace by importing data from another
> > workbook, however I need to clear out the original sheet since the data might
> > not use the same amount of rows.
> >
> > My workbook is 18mb.
> >
> > I can have 22000 rows to delete from column A to AE.
> >
> > I have tried the following, all work, however they take an age to do:
> >
> > ThisWorkbook.Sheets(data_type & " Data").Cells.ClearContents
> > or
> > ThisWorkbook.Sheets(data_type & " Data").Range("A1:AE22000").ClearContents
> > Note: This is a test and if it was quick then I would need to work out how
> > to change ("A1:AE22000") to use r instead of 22000 from below
> > r = .Range("A1").CurrentRegion.Rows.Count
> >
> > Is there anyway of doing this quickly, i.e. something that take less than a
> > minute. Mine takes several minutes.
> >
> >