-3

It is a simple code I got from running a macro but it takes a long time to process. Can anyone who's good at vba fix this please? Thanks.

Sub ClearAll()
    Application.ScreenUpdating = False
    Range("H2:H11").Select
    Selection.ClearContents
    Range("A2:A100").Select
    Selection.ClearContents
    Selection.ClearFormats
    Sheets(2).Select
    Cells.Select
    Selection.ClearContents
    ThisWorkbook.Sheets(3).Rows("2:" & Rows.Count).Delete
    Sheets(1).Select
    Range("A2").Select
    ActiveSheet.UsedRange
    ThisWorkbook.Save
    Application.ScreenUpdating = True
End Sub
BLkrn
  • 77
  • 2
  • 14
  • 8
    (Bursts out in tears.) – Mike Gledhill Jun 23 '15 at 14:21
  • 1
    `Can anyone who's good at vba fix this please?` I am not good in VBA but I can point you in the [Right Direction](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) Please try and incorporate the suggestions. If you are still stuck then post the new code – Siddharth Rout Jun 23 '15 at 14:33
  • 1
    How was your last 23 days with Excel and VBA? – bonCodigo Jun 23 '15 at 14:34

1 Answers1

0

Somewhat better...

Sub ClearAll()
    Application.ScreenUpdating = False
    Range("H2:H11").ClearContents
    With Range("A2:A100")
        .ClearContents
        .ClearFormats
    End With
    Sheets(2).Cells.ClearContents
    Sheets(3).Rows("2:" & Rows.Count).Delete
    Sheets(1).UsedRange.Select
    ThisWorkbook.Save
    Application.ScreenUpdating = True
End Sub
rohrl77
  • 3,277
  • 11
  • 47
  • 73
  • Hmm, I have seen people do this before. Is there a point to your second with statement? You only use it once. Why not just use `Sheets(2).Cells.ClearContents`? – vacip Jun 23 '15 at 14:56
  • Nah... you're right. I put it because when I started correcting the code, I thought there would be more statements coming... it's superfluous. – rohrl77 Jun 23 '15 at 15:08
  • Updated based on your comment – rohrl77 Jun 23 '15 at 15:08