Making a Nonconsecutive range of data consecutive

[adambush4242]

I have a range of data of x and y values. Each point has a checkbox. If
this checkbox is checked on, I want to include the data in a linest type
function, however in order to do this the data has to be in consecutive rows.
I have about 15 points to choose from, and all or none of them can be used
at anytime. I've tried using if statments but it is much too complicated for
this. Any ideas?

Thanks

Adam Bush

 

[JLatham]

You could probably keep your checkboxes and deal with this with VBA code (a
Macro) that would have to be written.

An option would be to add a column and put an X in it or leave it blank
instead of using the checkboxes and then filter the data to display only the
rows with an X in a column.

 

[adambush4242]

Is there anyway to take care of this on the worksheet level? If not, could
you point me in the right direction on what the code should be?

Thanks

Adam Bush

 

[JLatham]

I'm not sure what you mean by taking care of this at the worksheet level.

If you used the checkboxes from the Controls Toolbox you could associate
code like below (which is only set as an example with 4 possible 'raw' X/Y
values) along with a couple of routines to move the raw values in and out of
a 2 column area that would be the area actually referenced for your other use.

By using checkboxes from the Controls Toolbox you can double-click on them
in the design mode and you'll get a 'stub' for a sub associated at the
workbook level that you can put code into to respond to a click to each. As
you can see from the code, the row associated with each checkbox is passed to
one of 2 routines that either adds the X/Y pair to the data area or removes
them from it. All of this code is in the worksheet's code module.

Private Sub CheckBox1_Click()
If CheckBox1 = True Then
'is checked
'move info to data area
AddToList 2 ' 2 is the row number
Else
RemoveFromList 2 ' 2 is row number
End If
End Sub

Private Sub CheckBox2_Click()
If CheckBox2 = True Then
'is checked
'move info to data area
AddToList 3 ' 3 is the row number
Else
RemoveFromList 3 ' 3 is row number
End If
End Sub

Private Sub CheckBox3_Click()
If CheckBox3 = True Then
'is checked
'move info to data area
AddToList 4 ' 4 is the row number
Else
RemoveFromList 4 ' 4 is row number
End If
End Sub

Private Sub CheckBox4_Click()
If CheckBox4 = True Then
'is checked
'move info to data area
AddToList 5 ' 5 is the row number
Else
RemoveFromList 5 ' 5 is row number
End If
End Sub

Private Sub AddToList(rowNum As Long)
'data area is in columns J and K
'beginning at row 2
'have to find first row available
'in that range and move the data
'from the indicated row in columns B&C
'into that row
Dim nextRow As Long

nextRow = Range("J" & Rows.Count).End(xlUp).Row + 1
Range("J" & nextRow) = Range("B" & rowNum)
Range("K" & nextRow) = Range("C" & rowNum)
End Sub

Private Sub RemoveFromList(rowNum As Long)
'find the data entries in the data area
'and remove the pair from the list and
'move all cells below them up 1 row
Dim xValue As Double
Dim yValue As Double
Dim lastRow As Long ' last used row in data area
Dim dataRow As Long ' where data is found in data area
Dim dataArea As Range ' will refer to used cells in col J
Dim anyXValue As Range

xValue = Range("B" & rowNum)
yValue = Range("C" & rowNum)
lastRow = Range("J" & Rows.Count).End(xlUp).Row
If lastRow < 2 Then
Exit Sub ' no data in the data area
End If
Set dataArea = Range("J2:J" & lastRow)
For Each anyXValue In dataArea
If anyXValue = xValue And _
anyXValue.Offset(0, 1) = yValue Then
anyXValue.Offset(0, 1).Delete shift:=xlUp
anyXValue.Delete shift:=xlUp
Exit For
End If
Next
End Sub