Clear certain cells after a question

[kapfrank]

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.

 

[Smitty]

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

 

[kapfrank]

Thanx fot the quick respons

This is what i was looking for.

 

[kapfrank]

<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.

 

[erik.van.geit]

Hello, kapfrank,
Welcome to the Board !!!

try this

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

 

[Nimrod]

Hello kapfrank...

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

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