If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Making a Nonconsecutive range of data consecutive
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 |
#2
|
|||
|
|||
Making a Nonconsecutive range of data consecutive
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. " wrote: 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 |
#3
|
|||
|
|||
Making a Nonconsecutive range of data consecutive
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" wrote: 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. " wrote: 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 |
#4
|
|||
|
|||
Making a Nonconsecutive range of data consecutive
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 " wrote: 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" wrote: 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. " wrote: 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 |
Thread Tools | |
Display Modes | |
|
|