asked on

Macro Modification to refresh non-contiguous Cells in a Worksheet

EE Pros,

I have a WS that has a number of cells that I would like to clear after using the WS. The cells are not contiguous and I have a number of them (around 25) that need to be cleared. Presently, the only way I know how to clear them is to add a .Range("CELL#")="" line of code for each cell. This could take quite some time. Additionally, if I have a modification to the Worksheet, all of those cells may shift and I would have to update the Macro each time.

Sub ModelReset()
Application.EnableEvents = False
Application.ScreenUpdating = False
With Sheet7
.Range("E18") = ""
End With
End Sub

Here are the two asks;

1.) Is there a simple way of identifying the cells in one line of code where I don't have to add ".Range" to each instance?

and more importantly,

2.) Is there a way of using something like Range Names to insure that as changes to the WS (e.g. added rows or columns), I won't have to adjust the Macro each time?

Thank you in advance.

B.

Shums Faruk

Hi B,

Here are two asks:)

1. Is there any condition of clearing those cells?
2. What are those range, from which row? which column?

Below is sample with condition, I have explained where you need to make changes:

Sub ModelReset()
Dim Ws As Worksheet
Dim LR As Long
Dim i
Application.EnableEvents = False
Application.ScreenUpdating = False
Set Ws = Worksheets("Sheet7") 'Change Sheet7 to original sheet name
LR = Ws.Range("A" & Rows.Count).End(xlUp).Row 'Change "A" with whichever column has the maximum number of rows
For i = 2 To LR 'Change 2 to what row you want to clear the cell
    If Ws.Cells(i, 1).Value = "No" Then 'Change 1 to column number for which you need to apply condition, change "No" to the condition you want to apply
        Ws.Cells(i, 5).Value = "" 'Change 5 to column number which you want to clear cells. 5 stands for E Column
    End If
Next
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Open in new window

Bright01

ASKER

Shums,

Thanks for jumping in! I've dummied up a sheet for you to take a look at. I got an "Out of range" error when I tried to apply your code. I wanted you to see exactly what I'm looking at in terms of trying to clear these non-contiguous, numerical (not Textual) data (not formulas) only.

B.
Clear-Cells.xlsm

Rgonzo1971

Hi,

pls try

Sub macro()
Set Rng2 = Nothing
Set Rng1 = Union(Range(Range("N33"), Range("N" & Rows.Count).End(xlUp)), Range(Range("R33"), Range("R" & Rows.Count).End(xlUp)))
On Error Resume Next
Set Rng2 = Rng1.SpecialCells(xlCellTypeConstants, xlNumbers)
On Error GoTo 0
If Not Rng2 Is Nothing Then Rng2.ClearContents
End Sub

Open in new window

EDIT Corrected code

Regards

Shums Faruk

Hi B,

Try below:

Sub ModelReset()
Dim Ws As Worksheet
Dim LR As Long
Dim StrTot As String, StrPerc As String
Dim i As Integer
Application.ScreenUpdating = False
Set Ws = ActiveSheet
LR = Ws.Range("O" & Rows.Count).End(xlUp).Row
StrTot = "What is the total"
StrPerc = "By what percentage"

For i = 1 To LR
    If Ws.Cells(i, 6).Value Like StrTot & "*" Then
        Ws.Cells(i, 14).Value = ""
    ElseIf Ws.Cells(i, 6).Value Like StrPerc & "*" Then
        Ws.Cells(i, 18).Value = ""
    End If
Next i
Application.ScreenUpdating = True
End Sub

Open in new window

Clear-Cells_v1.xlsm

Bright01

ASKER

Shums, When I run the macro, nothing happens. Did you get to run it in the file I sent?

B.

Shums Faruk

Yes.

I tested and sent you.

Where you pasted the code? Have you tried clicking on button to clear on the sample I sent you back?

Shums Faruk

See screenshot
Before:

After:

Bright01

ASKER

Shums,

Interesting... when I launched it the second time, I did see the button and it almost works.

However, when I review how you did this and try to scale it, I realize that you are identifying the cells to clear based on the text in the forth column. This won't work since I could easily change the question and then it would break. Is there any other way? Can I somehow identify the cells and they will change as rows are added? I'm back to the original question.

B.

SOLUTION

Shums Faruk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Simulog

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Rgonzo1971

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bright01

ASKER

Great ideas guys! Shums, thanks for all of the iterations. Rgonzo, always appreciate your efforts and work. At the end of the day, I went with Simulog's code because it was so simple! I did not know I could create a range at the individual cell level for non-contiguous cells! Then when I tested it by changing/adding and deleting some rows, it still worked. Very nice.

Thanks to all of you.

B.