ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   How to clear cells fast? (https://www.excelbanter.com/excel-programming/374540-how-clear-cells-fast.html)

Jared
How to clear cells fast?
 
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

Nigel
How to clear cells fast?
 
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

--
Cheers
Nigel



"Jared" 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



Jared
How to clear cells fast?
 
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

"Nigel" wrote:

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

--
Cheers
Nigel



"Jared" 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




Jared
How to clear cells fast?
 
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

"Nigel" wrote:

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

--
Cheers
Nigel



"Jared" 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




Jim Cone
How to clear cells fast?
 
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"
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

Dave Peterson
How to clear cells fast?
 
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

Jared wrote:

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

--

Dave Peterson

Jared
How to clear cells fast?
 
good idea, but i don't know how much time that can save

thanks

"Dave Peterson" wrote:

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

Jared wrote:

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

--

Dave Peterson

Jared
How to clear cells fast?
 
Speechless...........

Bravo!!!!!!

THANK YOU!

"Jim Cone" wrote:

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"
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

Tom Ogilvy
How to clear cells fast?
 
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.

--
Regards,
Tom Ogilvy
"Jared" wrote in message
...
good idea, but i don't know how much time that can save

thanks

Dave Peterson
How to clear cells fast?
 
Timing is everything!

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



Tom Ogilvy wrote:

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.

--
Regards,
Tom Ogilvy
"Jared" wrote in message
...
good idea, but i don't know how much time that can save

thanks


--

Dave Peterson


All times are GMT +1. The time now is 05:39 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com