VBA Clearing Sheet Contents

It macro does not finish just keeps running then i end it manually. Also if i do it manually the same problem occurs the processing does not complete eventually i have to kill the task.

Does it work if you do it bit by bit?

application.screenupdating = false
application.enableevents = false
application.calculation = xlmanual
dim lcol, lrow, count
With Sheets("Master Excel")    
lcol = .Range("A2").end(xltoright).column
lrow = .range("A2").end(xldown).column
for count = 502 to lcol step 500
  .Range("A" & count-500 & ":" & cells(count,lcol).address).clearcontents
next
end with
application.screenupdating = true
application.enableevents =true
application.calculation = true

or something along those lines.

Perhaps you can try:

Sheets("Master Excel").Range("A2", Sheets("Master Excel").Cells.Specialcells(xlcelltypelastcell)).Clear

instead of this:

Sheets("Master Excel").Select
    Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Range("A2").Select

@Izandol & @yudlugar they did not work I am afraid. My concern its the file not the macro as i use the same macro in another file and works perfectly with 30,000 lines of data over 43 columns.

Even manually if i try to insert a row for example the file locks in (Not Responding) and i need to kill the task. Is there something in the excel causing this problem that you might have experience with. There is 14 sheets in the workbook but the Master sheet has all the detail combined with formulas that is about 1,000,000 cells with content, i have never seen this before in excel crashing constantly like this.

What is the size of your workbook?

11mb and after the formulas are executed its 25mb

I have another macro which i run after clearing with the new data that formats the data, then does HLookup formulas in the Master Excel sheet, this is working although the copy paste function there takes time also.