Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > How to clear cells fast?
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 109
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 923
Default 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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 109
Default 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



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 109
Default 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



Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,290
Default 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
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default 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
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 109
Default 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

Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 109
Default 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

Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default 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



Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default 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
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
how to fast format cells? sql_dba Excel Discussion (Misc queries) 3 August 14th 06 08:40 PM
A fast way to fill cells in Excel Tas Excel Programming 7 June 18th 06 06:41 PM
cannot make the excel fill in/delete cells fast Johny B. Excel Programming 1 September 21st 05 06:03 PM
Fast way to clear Listbox selection [email protected] Excel Programming 4 August 26th 05 10:09 PM
Fast way to search many cells by column for text strings Mikee Excel Discussion (Misc queries) 2 July 1st 05 06:44 PM


All times are GMT +1. The time now is 10:00 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement