VBA - Checkbox with variable range

[TheOneTrueScotsman]

(I apologize if this topic has already been addressed elsewhere)

I am very new to VBA scripting, but I have managed to write the following:

Private Sub Unused_02_Click()
If Sheet1.Unused_02.Value = True Then
Sheet1.Range("C18:C117") = "X"
Else
Sheet1.Range("C18:C117") = ""
End If
End Sub

Context:

I work in the quality department of a machine shop, and I have a form I use to record part screening data consisting of 10 columns and 100 rows in order to accommodate large batches of parts. All ten columns in each row must have an input in order for PASS-FAIL evaluation to occur, and I use X as a placeholder when I have no data. I use checkboxes at the top of columns C through K [my 2 through 10] to automatically fill in said placeholder when needed (column B [my 1] is intentionally left out as it would not make sense to have one there). The script above is a sample of one of the checkboxes. It populates a fixed range, and though this is adequate, it leaves a lot of junk placeholders in the end. My request is that I'd like it to only populate as far as there is data in column B.

For instance, if I use only 20 rows of the 100 available, the columns that are checked would populate Xs only to those 20 and leave the other 80 blank. If I were to go to the next row and provide an input, the checked columns would fill just the other cells of that row in at that time, and also delete the Xs of that row were I to clear the input I just made. Thank you for any assistance you have to offer.

 

[davesexcel]

dim LstRw as long
with Sheet1
LstRw=.cells(.rows.count,"B").end(xlup).row
.Range("C18:C" & Lstrw) = "X"
Else
.Range("C18:C" & Lstrw) = ""
End If

 

[TheOneTrueScotsman]

dim LstRw as long
with Sheet1
LstRw=.cells(.rows.count,"B").end(xlup).row
.Range("C18:C" & Lstrw) = "X"
Else
.Range("C18:C" & Lstrw) = ""
End If

I keep getting "Compile Error: Else without If".

 

[davesexcel]

Well I'm sure you can see why, I was showing you what lines to change

 

[TheOneTrueScotsman]

Well I'm sure you can see why, I was showing you what lines to change

After tinkering with the script you provided, I got the following:

Private Sub Unused_02_Click()
Dim LstRw As Long
With Sheet1
If Sheet1.Unused_02.Value = True Then
LstRw = .Cells(.Rows.Count, "B").End(xlUp).Row
.Range("C18:C" & LstRw) = "X"
Else
.Range("C18:C" & LstRw) = ""
End If
End With
End Sub

It would perform as I wanted it to when I checked the box, but it would return a "Run-time error '1004': method 'Range' of object '_Worksheet' failed" message with ".Range("C18:C" & LstRw) = """ highlighted in the debug. I changed it to "Sheet1.Range("C18:C117") = """ like I had in my initial script and it worked beautifully.

Thank you so much for your help with this. It's been the bane of my existence for the past several days.

 

[TheOneTrueScotsman]

There is one last bit with which I need assistance.

I have a header at in cell 17 of each column, and the script is overwriting it with an X if there is nothing in B18. This wouldn't be a problem if I didn't need to leave said cells in a protected state as a simple ".Range("C17") = "Dimension 2"" right before the "Else" works, but unfortunately this is not the case as they need to remain protected. Thank you.

 

[davesexcel]

LstRw = .Cells(.Rows.Count, "B").End(xlUp).Row + 1

This would make it 18 if the last row as 17

-Would you have a different column you could count the rows?

-You could add an if statement as well.

if Lstrw=17 then Lstrw=18

 

[TheOneTrueScotsman]

LstRw = .Cells(.Rows.Count, "B").End(xlUp).Row + 1

This would make it 18 if the last row as 17

-Would you have a different column you could count the rows?

-You could add an if statement as well.

if Lstrw=17 then Lstrw=18

Thanks to you my form now well and truly works perfectly. I am grateful for your assistance throughout all of this.