PC Review
Home Forums Reviews Articles Register
Member Login:

PC Review > Forums > Newsgroups > Microsoft Excel > Microsoft Excel Programming > How to clear cells fast?

Reply
Thread Tools Rate Thread

How to clear cells fast?

 
 
=?Utf-8?B?SmFyZWQ=?=
Guest
Posts: n/a
 
      7th Oct 2006
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 Quote
 
 
 
 
Nigel
Guest
Posts: n/a
 
      7th Oct 2006
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" <(E-Mail Removed)> wrote in message
newsF54FD32-C1DD-4FB9-A2A8-(E-Mail Removed)...
>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 Quote
 
=?Utf-8?B?SmFyZWQ=?=
Guest
Posts: n/a
 
      7th Oct 2006
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" <(E-Mail Removed)> wrote in message
> newsF54FD32-C1DD-4FB9-A2A8-(E-Mail Removed)...
> >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 Quote
 
=?Utf-8?B?SmFyZWQ=?=
Guest
Posts: n/a
 
      7th Oct 2006
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" <(E-Mail Removed)> wrote in message
> newsF54FD32-C1DD-4FB9-A2A8-(E-Mail Removed)...
> >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 Quote
 
Jim Cone
Guest
Posts: n/a
 
      7th Oct 2006
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" <(E-Mail Removed)>
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 Quote
 
Dave Peterson
Guest
Posts: n/a
 
      7th Oct 2006
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 Quote
 
=?Utf-8?B?SmFyZWQ=?=
Guest
Posts: n/a
 
      7th Oct 2006
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 Quote
 
=?Utf-8?B?SmFyZWQ=?=
Guest
Posts: n/a
 
      7th Oct 2006
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" <(E-Mail Removed)>
> 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 Quote
 
Tom Ogilvy
Guest
Posts: n/a
 
      7th Oct 2006
>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" <(E-Mail Removed)> wrote in message
news:EA958F67-D39E-48AE-84DD-(E-Mail Removed)...
> good idea, but i don't know how much time that can save
>
> thanks
>
 
Reply With Quote
 
Dave Peterson
Guest
Posts: n/a
 
      7th Oct 2006
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" <(E-Mail Removed)> wrote in message
> news:EA958F67-D39E-48AE-84DD-(E-Mail Removed)...
> > good idea, but i don't know how much time that can save
> >
> > thanks
> >

--

Dave Peterson
 
Reply With Quote
 
 
 
Reply

« sumproduct and if | array variable is empty? »
Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Lookup cells in one column and clear cells in another JoshW0000 Microsoft Excel Programming 5 3rd Sep 2009 03:01 PM
Create a Clear button to clear unprotected cells Jcraig713 Microsoft Excel Programming 2 26th Nov 2007 03:55 PM
clear contents cells of unprotected cells =?Utf-8?B?RWQ=?= Microsoft Excel Programming 6 12th Jan 2006 06:09 PM
Re: Fast way to clear Listbox selection Doug Glancy Microsoft Excel Programming 0 26th Aug 2005 07:47 PM
Warning/Error dialogue boxes clear to fast Gary Windows XP General 1 4th Jul 2004 12:46 PM


Features
Forums Startup Database Reviews Articles FAQ / Guidelines About Us
 

Advertising
 

Newsgroups
Windows Vista Windows XP Microsoft Word Microsoft DotNet Windows 2000 AntiSpyware Microsoft Access Microsoft Excel Microsoft Outlook Hardware
 


All times are GMT +1. The time now is 01:29 PM.
Powered by vBulletin®. Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2010, Crawlability, Inc.
Contact Us - Archive - Privacy Statement - Top