Clear only value not formulas vba

Jefersen

New Member
Joined
Sep 2, 2022
Messages
6
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
Hey peeps,
I'm trying to use this vba on my sheet but I want to clear only the cells without a formula.
This is the code that i'm using.

The objective is when cel C is valued "IN", it would clear all entry in the range D through J that doesn't contain formulas.
If there is a simpler way in doing this, please don't hasitate to tell me. :)

Thanks in advance for the help.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myLastRow As Long

Dim i As Long
Application.ScreenUpdating = False

' Find last row

myLastRow = Cells(Rows.Count, "C").End(xlUp).Row

' Loop through range

For i = 6 To myLastRow

If Cells(i, "C").Value = "IN" Then Range(Cells(i, "D"), Cells(i, "J")).ClearContents

Next i

Application.ScreenUpdating = True
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Hi,

welcome to the forum, does this gets you what you want :

Excel Formula:
If Cells(i, "C").Value = "IN" Then Range(Cells(i, "D"), Cells(i, "J")).SpecialCells(xlCellTypeConstants, 23).Clear

cheers
Rob
 
Upvote 0
Thanks for the replay but it gives an error
 

Attachments

  • error1.jpg
    error1.jpg
    45 KB · Views: 7
Upvote 0
It also looks like this code is using to much memory.
Can someone help to rewrite is to a certain range? Like D6:I600
 
Upvote 0
ok, sorry, I can see that it fails if it does not find any formulas to delete for some reaosn.
So I added 1 line.

VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myLastRow As Long

Dim i As Long
Application.ScreenUpdating = False

' Find last row

myLastRow = Cells(Rows.Count, "C").End(xlUp).Row

' Loop through range

For i = 6 To myLastRow

On Error Resume Next
If Cells(i, "C").Value = "IN" Then Range(Cells(i, "D"), Cells(i, "J")).SpecialCells(xlCellTypeConstants, 23).Clear

Next i

Application.ScreenUpdating = True

End Sub
 
Upvote 0
Solution
ok, sorry, I can see that it fails if it does not find any formulas to delete for some reaosn.
So I added 1 line.

VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myLastRow As Long

Dim i As Long
Application.ScreenUpdating = False

' Find last row

myLastRow = Cells(Rows.Count, "C").End(xlUp).Row

' Loop through range

For i = 6 To myLastRow

On Error Resume Next
If Cells(i, "C").Value = "IN" Then Range(Cells(i, "D"), Cells(i, "J")).SpecialCells(xlCellTypeConstants, 23).Clear

Next i

Application.ScreenUpdating = True

End Sub
It looks good, thanks for so far But it seems that the range is too big. Therefore it freezes Excel . Can you please limit the range for me to the D6:J600?
 
Upvote 0
Its getting the range from this row in your code :

Excel Formula:
myLastRow = Cells(Rows.Count, "C").End(xlUp).Row

which is basically saying find the last row of populated data in Column C.

I can only assume you have some data somewhere in your column C that you need to delete ? Try deleting it all first.
 
Upvote 0
Its getting the range from this row in your code :

Excel Formula:
myLastRow = Cells(Rows.Count, "C").End(xlUp).Row

which is basically saying find the last row of populated data in Column C.

I can only assume you have some data somewhere in your column C that you need to delete ? Try deleting it all first.
Thanks a lot. It works perfectly now! :)
 
Upvote 0
Great, thanks for the feedback, glad to have been of help. I guess you found your "rogue" data somewhere then ..
Rob
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top