How to clear cells fast?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a macro which will clear a whole bunch of cells from a whole bunch of
sheets. It takes forever. I do not know if there is a better/faster way to do
this. One idea of mine is to select muliple cells and then clear the selected
cells instead of one by one.
There might be a better way. Any suggestions?

Sub Clear_The_Month()


' Clear_The_Month Macro
' Macro recorHeH 9/14/2006 by Rapture Marketing
'

'
Dim c As Integer
Dim r As Integer
Dim sr As Integer
Dim s As Integer
Dim lw As Integer



Application.ScreenUpdating = False

lw = 16
For s = 5 To 36
If s >= 31 Then lw = 9
If Sheets(s).Name <> "salaries" And Sheets(s).Name <> "salaries 2"
Then
For c = 4 To lw Step 2
For r = 2 To 74 Step 6
For sr = 1 To 4
If Not IsEmpty(Worksheets(s).Cells((sr + r), c))
Then Worksheets(s).Cells((sr + r), c).ClearContents
Next sr
Next r
Next c
For c = 4 To lw Step 2
For r = 83 To 87 Step 2
If Not IsEmpty(Worksheets(s).Cells(r, c)) Then
Worksheets(s).Cells(r, c).ClearContents
Next r
Next c
For c = 4 To lw Step 2
For r = 95 To 193 Step 2
If Not IsEmpty(Worksheets(s).Cells(r, c)) Then
Worksheets(s).Cells(r, c).ClearContents
Next r
Next c
End If
Next s

End Sub

Thanks
 
Yes, act on ranges

e.g

Sheets("Sheet1").Range("A4:Z26").ClearContents

or to set row / columns in loops etc .....

With Sheets("Sheet1")
.Range(.Cells(4,1),.Cells(26,26)).ClearContents
End With

or to remove all cells in a sheet

Sheets("Sheet1").Cells.ClearContents
 
Unfortunatly, i am not able to wipe out the sheet. i have specific which i am
able to delete.
The question is how to select multiple cells in a loop and then delete them
after the loop is done?

Jared
 
Unfortunatley, i am not able to wipe out the sheet. I have specific cells
which i am able to delete. The question is how do i select mulitple cells
through a loop and delete all after the loop is done?

Thanks,
Jared
 
No real way for me to test this, you can give it a try....
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

Sub Clear_The_Month()
' Macro recorHeH 9/14/2006 by Rapture Marketing
Dim c As Integer
Dim r As Long
Dim sr As Long
Dim s As Integer
Dim lw As Integer

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

lw = 16
For s = 5 To 36
If s >= 31 Then lw = 9
If Sheets(s).Name <> "salaries" And Sheets(s).Name <> "salaries 2" Then
For c = 4 To lw Step 2
For r = 2 To 74 Step 6
For sr = 1 To 4
Worksheets(s).Cells((sr + r), c).ClearContents
Next sr
Next r
For r = 83 To 87 Step 2
Worksheets(s).Cells(r, c).ClearContents
Next r
For r = 95 To 193 Step 2
Worksheets(s).Cells(r, c).ClearContents
Next r
Next c
End If
Next s
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
'-------------


"Jared" <[email protected]>
wrote in message
I have a macro which will clear a whole bunch of cells from a whole bunch of
sheets. It takes forever. I do not know if there is a better/faster way to do
this. One idea of mine is to select muliple cells and then clear the selected
cells instead of one by one.
There might be a better way. Any suggestions?

Sub Clear_The_Month()
' Clear_The_Month Macro
' Macro recorHeH 9/14/2006 by Rapture Marketing
'
Dim c As Integer
Dim r As Integer
Dim sr As Integer
Dim s As Integer
Dim lw As Integer
Application.ScreenUpdating = False

lw = 16
For s = 5 To 36
If s >= 31 Then lw = 9
If Sheets(s).Name <> "salaries" And Sheets(s).Name <> "salaries 2"
Then
For c = 4 To lw Step 2
For r = 2 To 74 Step 6
For sr = 1 To 4
If Not IsEmpty(Worksheets(s).Cells((sr + r), c))
Then Worksheets(s).Cells((sr + r), c).ClearContents
Next sr
Next r
Next c
For c = 4 To lw Step 2
For r = 83 To 87 Step 2
If Not IsEmpty(Worksheets(s).Cells(r, c)) Then
Worksheets(s).Cells(r, c).ClearContents
Next r
Next c
For c = 4 To lw Step 2
For r = 95 To 193 Step 2
If Not IsEmpty(Worksheets(s).Cells(r, c)) Then
Worksheets(s).Cells(r, c).ClearContents
Next r
Next c
End If
Next s
End Sub
Thanks
 
You're starting over in your loop 2 additional times.

For c = 4 To lw Step 2

appears 3 times in your code.

Why not just stay in that loop and do the other stuff, too:

Sub Clear_The_Month()

Dim c As Long
Dim r As Long
Dim sr As Long
Dim s As Long
Dim lw As Long

Application.ScreenUpdating = False

lw = 16
For s = 5 To 36
If s >= 31 Then lw = 9
If Sheets(s).Name <> "salaries" And Sheets(s).Name <> "salaries 2" Then
For c = 4 To lw Step 2
For r = 2 To 74 Step 6
For sr = 1 To 4
If Not IsEmpty(Worksheets(s).Cells((sr + r), c)) Then
Worksheets(s).Cells((sr + r), c).ClearContents
End If
Next sr
Next r
For r = 83 To 87 Step 2
If Not IsEmpty(Worksheets(s).Cells(r, c)) Then
Worksheets(s).Cells(r, c).ClearContents
End If
Next r
For r = 95 To 193 Step 2
If Not IsEmpty(Worksheets(s).Cells(r, c)) Then
Worksheets(s).Cells(r, c).ClearContents
End If
Next r
Next c
End If
Next s

End Sub
 
good idea, but i don't know how much time that can save

I guess about as much as Jim's suggestion, and you were speechless over
that.
 
Timing is everything!

Q: What's the most important thing about com(edy....)
A: (interrupting) Timing!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

how to clear cells quick? 1
Compare 2 excel sheets -Error Checking of entered names 1
VBA set thick borders around non empty cells in range 0
Run macro on all files in a specific folder 3
Trouble with Clear Contents Macro 9
Hidden sheet bleeds through 3
vlookup issue 2
setting cell values in another worksheet? 1

Back
Top