VBA range selection / clearing error

Question

I'm trying to clear a certain row on the columns A:H and K in my sheet using vba. In column K there's not only a value, also a checkbox. I'd like to leave column I en J as they are since there's a formula in those rows.

Now I've tried a lot of different options found shattered on the internet, but can't seem to fix the problem.

My code is as following:

Sub ClearSelected()

Sheets("overview").Unprotect
Sheets("Database").Unprotect

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim rng As Range
Dim counter As Integer
Dim vert As Integer
Dim r As Range
Dim chkbx As CheckBox

Set ws1 = Worksheets("Overview")
Set ws2 = Worksheets("Database")
Set rng = ws1.Range("P2")

vert = rng.Value + 1
counter = 2

'do Loop
Worksheets("Database").Activate
Do While counter < vert

    'if "True", remove row
    If ws2.Range(ws2.Range("K" & counter)) = True Then
        ws2.Range("A" & counter & ":H" & counter).Select
        Selection.Clear

        ws2.Range("K" & counter).Select
        Selection.Clear


        'Remove checkbox in selectie
        Set r = Selection
        For Each chkbx In ActiveSheet.CheckBoxes
            If Not Intersect(r, chkbx.TopLeftCell) Is Nothing Then chkbx.Delete
        Next chkbx


        rng.Value = rng.Value - 1
    'remove checkbox
    End If
 counter = counter + 1

 Loop
 Sheets("overview").Protect AllowUsingPivotGraphs:=True
Sheets("Database").Protect

End Sub

For some reason it's failing on the range selection/clearing. I'm getting the errormessage 1004.

Hope you have a good suggestion for me.

[INTERESTING READ](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) — Siddharth Rout, Dec 16 '13 at 23:04
Can you specify what does it mean "failing"? Any error message? Did you use breakpoints and debug your code? Did you compile prior to running? Please be as specific as possible, do not let others guess. — sancho.s ReinstateMonicaCellio, Dec 16 '13 at 23:39
I got errormessage 1004 (added to first post). Error starts at the point where I select the Range Ax:Hx. I did not compile prior to running the code. — Stefan, Dec 17 '13 at 09:36
@Stefan - 1) Post the complete error message, 2) Replace `ws2.Range("A" & counter & ":H" & counter).Select` by `Dim s1 as string` `s1="A" & counter & ":H" & counter`, `ws2.Range(s1).Select`, 3) compile, use breakpoints, debug. Repost whenever you had done all this. — sancho.s ReinstateMonicaCellio, Dec 17 '13 at 10:07
Roughly translated: Error 1004 while executing: error defined by object or application. All does fine up until `ws2.range(s1).select`. Next line gives this error, no matter what is in there. — Stefan, Dec 17 '13 at 10:19

score 0 · Answer 1 · answered Dec 17 '13 at 13:42

I'm not entirely sure if this will work, but have you tried changing the select.clear lines to something like

 ws2.Range("K" & counter).ClearContents

This clears the cells without affecting the formatting, which might possibly causing the issue. Sometimes 1004 is also down to a loss of focus on an object, or the size of the range you are trying to operate on.

BTW, you may also not need to worry too much about deleting formulas. You can re-insert them automatically.

Something like:

 ws2.Range("K" & counter).FormulaR1C1="=(R+1*C+1)"
'R and C refer to row and column, so equations remain relatively referenced

The easiest way is to start recording a macro, input the desired formula into a cell, and then view the code to find out how to structure the formula in VBA.

Not sure if any of this will help, but here's hoping.

VBA range selection / clearing error

1 Answers1