Clear certain cells after a question

kapfrank

Board Regular
Joined
Nov 16, 2005
Messages
112
I have a large worksheet and there is a lot of data in it.
In a row there are cells which contains data en other contains formulas.

If i want to clear a row i have to select all the cells with data and clear them.

Is it possible to make a macro that can do the following thing.
- Ask which row has to be cleaned (row=15)
- And then only specific cells have to be cleaned (Cel B15, D15 and H15).

Thanx.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Welcome to the Board!

Is it always B, D & H, or is there some other criteria, like cells without formulas?

If it's the former, something like this might be a start:

<font face=tahoma><SPAN style="color:#00007F">Sub</SPAN> foo()
    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
    
    i = Application.InputBox("Please Enter the Row #", "Delete Data", Type:=1)
    
    Range("B" & i).ClearContents
    Range("D" & i).ClearContents
    Range("H" & i).ClearContents

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

Hope that helps,

Smitty
 
Upvote 0
pennysaver said:
<font face=tahoma><SPAN style="color:#00007F">Sub</SPAN> foo()
    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
    
    i = Application.InputBox("Please Enter the Row #", "Delete Data", Type:=1)
    
    Range("B" & i).ClearContents
    Range("D" & i).ClearContents
    Range("H" & i).ClearContents

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

This is working perfect except for one thing. :-?
If i push the 'cancel' button i get an error.
I think that that is because there is no instruction for 'cancel' button.
Could somebody help me to fix this.

Thank You.
 
Upvote 0
Hello, kapfrank,
Welcome to the Board !!!

try this
Code:
Option Explicit

Sub test()
Dim I As Variant

    Do
    I = ""
    I = Application.InputBox("Please enter rownumber", "TITLE", "", Type:=1)
      If I = False Then Exit Sub
    Loop While I = ""
    
    Range("B" & I).ClearContents
    Range("D" & I).ClearContents
    Range("H" & I).ClearContents
End Sub

kind regards,
Erik

EDIT: all at once
Range("B" & I & ",D" & I & ",H" & I).ClearContents
 
Upvote 0
Hello kapfrank...

The following code only removes values from cells that have constants . It does not clear cells with formula.

Code:
Public Sub ClearOnlyConstants()
    msg = "Please Type in Row you'd like cleared "
    
    rw = Application.InputBox(msg, "Clear Constants", Type:=1)
    If rw = False Then Exit Sub
    
    On Error Resume Next
    Rows(rw & ":" & rw).SpecialCells(xlCellTypeConstants, 3).ClearContents

End Sub
 
Upvote 0

Forum statistics

Threads
1,224,351
Messages
6,178,060
Members
452,822
Latest member
MtC

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