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.