Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to clear cells quick?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to clear cells quick?
If the cells you want to clear are always the same you may consider using
Application.union For example: dim ClearRange as range set ClearRange = application.union(range("A1"),range("F6")) ClearRange.clearcontents 'continue setting ClearRange for each set of cells you want to clear. You shouldn't have to test if the cell is empty since you are going to clear it anyway. -- JNW "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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
FORMATTING CELLS FOR QUICK INPUT | Excel Worksheet Functions | |||
clear contents cells of unprotected cells | Excel Programming | |||
Quick question - quick answer about assigning shortcut keys | Excel Programming | |||
Clear cells range if certain cells are all empty | Excel Programming | |||
Quick Question with selecting cells | Excel Programming |