Hey,
My code is supposed to delete a certain piece of data from the sheet. It finds this data using two criteria: if the sizes when subtracted are within 0.3 of each other and if the heights when divided are 30% or bigger. For some reason the size function works, but the height function is not working because i do not think its selecting the correct cells. Any help would be appreciated. The sheet is set up like this and this is one example of data that would be deleted (they wouldnt be next to each other like this the code would search for it).
<tbody>
</tbody>
Sub BTfindelete()
Dim startRow As Double
Dim endRow As Double
Dim startCol As Double
Dim endCol As Double
Dim size As Double
Dim sizeOne As Double
Dim y As Double
Dim height As Double
Dim delete1 As Double
Dim delete2 As Double
Dim delete3 As Double
Dim alleleNumber As Variant
Dim repeater As Integer
Dim counter As Integer
Dim amount As Double
Dim height1 As Double
amount = InputBox("How many samples are in your sheet?")
alleleNumber = InputBox("How many alleles in total do you have on your excel spreadsheet? If you would like to take a moment and see the number, enter 'y'")
If alleleNumber = "y" Then
Exit Sub
End If
repeater = 0
While repeater < (amount + 1)
counter = 16
startRow = 2 + (counter * repeater)
endRow = 17 + (counter * repeater)
startCol = 5
endCol = 150
For row = startRow To endRow
For col = startCol To endCol
For height = 1 To alleleNumber
If Cells(1, col) = "Height " & height Then
If Cells(row, col) >= 30 Then
Cells(row, col) = height1
size = Cells(row, col - 1)
Cells(row, col - 1).Select
For delete1 = startRow To endRow
For delete2 = startCol To endCol
For delete3 = 1 To alleleNumber
If Cells(1, delete2) = "Size " & delete3 Then
If Abs(size - Cells(delete1, delete2)) <= 0.3 Then
If (Cells(delete1, delete2 + 1).Value / height1) > 0.3 Then
Cells(delete1, delete2).Select
Cells(delete1, delete2).ClearContents
Cells(delete1, delete2 + 1).ClearContents
Cells(delete1, delete2 - 1).ClearContents
End If
End If
End If
Next delete3
Next delete2
Next delete1
End If
End If
Next height
Next col
Next row
repeater = repeater + 1
Wend
End Sub
My code is supposed to delete a certain piece of data from the sheet. It finds this data using two criteria: if the sizes when subtracted are within 0.3 of each other and if the heights when divided are 30% or bigger. For some reason the size function works, but the height function is not working because i do not think its selecting the correct cells. Any help would be appreciated. The sheet is set up like this and this is one example of data that would be deleted (they wouldnt be next to each other like this the code would search for it).
Allele | size | height |
14 | 118.44 | 14 |
13.2 | 118.24 | 3103 |
<tbody>
</tbody>
Sub BTfindelete()
Dim startRow As Double
Dim endRow As Double
Dim startCol As Double
Dim endCol As Double
Dim size As Double
Dim sizeOne As Double
Dim y As Double
Dim height As Double
Dim delete1 As Double
Dim delete2 As Double
Dim delete3 As Double
Dim alleleNumber As Variant
Dim repeater As Integer
Dim counter As Integer
Dim amount As Double
Dim height1 As Double
amount = InputBox("How many samples are in your sheet?")
alleleNumber = InputBox("How many alleles in total do you have on your excel spreadsheet? If you would like to take a moment and see the number, enter 'y'")
If alleleNumber = "y" Then
Exit Sub
End If
repeater = 0
While repeater < (amount + 1)
counter = 16
startRow = 2 + (counter * repeater)
endRow = 17 + (counter * repeater)
startCol = 5
endCol = 150
For row = startRow To endRow
For col = startCol To endCol
For height = 1 To alleleNumber
If Cells(1, col) = "Height " & height Then
If Cells(row, col) >= 30 Then
Cells(row, col) = height1
size = Cells(row, col - 1)
Cells(row, col - 1).Select
For delete1 = startRow To endRow
For delete2 = startCol To endCol
For delete3 = 1 To alleleNumber
If Cells(1, delete2) = "Size " & delete3 Then
If Abs(size - Cells(delete1, delete2)) <= 0.3 Then
If (Cells(delete1, delete2 + 1).Value / height1) > 0.3 Then
Cells(delete1, delete2).Select
Cells(delete1, delete2).ClearContents
Cells(delete1, delete2 + 1).ClearContents
Cells(delete1, delete2 - 1).ClearContents
End If
End If
End If
Next delete3
Next delete2
Next delete1
End If
End If
Next height
Next col
Next row
repeater = repeater + 1
Wend
End Sub