PDA

View Full Version : Selective Deletion of ranges



Sir Phoenix
10-07-2005, 02:53 PM
Hey guys... I have a range. In column A are specific row titles. (Daytime1, daytime2, Daytime3, evening1, evening2, overnight1, overnight2, staticBob, staticMike, etc.) These values are automatically populated in a combobox, cmbSchedName. Anyways, I have a remove button. Based on the value of the combobox... if static(whatever) is selected, it's supposed to find the row, and delete the range. That works fine. If any others are pressed, for the ones with numbers on the end, it's supposed to go through the list (which is sorted), find the LAST instance of the schedule type (daytime, evening, overnight), and delete THAT title, but the selected schedule's data. (So if you choose to delete Daytime3 and there are 5 daytime schedules, Daytime3's data will be deleted, and Daytime4 will become the new daytime3, and Daytime5 will become daytime4, and daytime5's cell will be deleted in place of daytime3's. I'm getting an unknown error on this, so if you can help... much appreciated!

Private Sub cmdRemove_Click()
Dim cellRange, myCell, fooCell As Range
Dim strChecker As String

With ThisWorkbook.Sheets(4)
Set cellRange = .Range("a2", .Cells(Rows.Count, "a").End(xlUp))
Set myCell = cellRange.Find(Me.cmbSchedName.Value, MatchCase:=True)
If Me.cmbSchedName.Value = "" Then
MsgBox ("Please select a schedule.")
Else
If myCell Is Nothing Then
MsgBox ("The selected schedule has been already deleted!")
Else
MsgBox ("Schedule " & Me.cmbSchedName.Value & " has been deleted!")
If chkStatic.Value = True Then
.Range(myCell, .Cells(myCell.Row, "i")).Delete
Else
.Range(.Cells(myCell.Row, "b"), .Cells(myCell.Row, "i")).Delete
strChecker = Left(Me.cmbSchedName.Value, 4)
For Each fooCell In cellRange
If Left(fooCell.Text, 4) = strChecker And _
Left(.Range(.Cells(fooCell.Row + 1, "a")).Text, 4) <> strChecker Then
Set myCell = fooCell
End If
Next fooCell
myCell.Delete
fillScheds
End If
End If
End If
End With
End Sub

malik641
10-07-2005, 03:35 PM
Private Sub cmdRemove_Click()
Dim cellRange, myCell, fooCell As Range
Hey Jamie,
If you're going to use cellRange, myCell and fooCell as Ranges, then you can't use that line because it only declares fooCell as a Range, and the rest Variants.

Try this, maybe it will help:

Dim cellRange As Range
Dim myCell As Range
Dim fooCell As Range

'OR

Dim cellRange As Range, myCell As Range, fooCell As Range


When you try to use variants for Ranges, you'll get an error (though I'm not sure exactly why you will).

See if that fixes your error :thumb

Bob Phillips
10-08-2005, 07:38 AM
If you're going to use cellRange, myCell and fooCell as Ranges, then you can't use that line because it only declares fooCell as a Range, and the rest Variants.

The second part of that statement is correct, but you can use variants without any problem because the data type is cast as Variant/Object/Range as soon as they are set.

The real problem is in the way the OP uses the data, in this line


If Left(fooCell.Text, 4) = strChecker And _
Left(.Range(.Cells(fooCell.Row + 1, "a")).Text, 4) <> strChecker Then
Set myCell = fooCell
End If


because the default property of a range is the value, so it is getting the .Cells(fooCell.Row + 1, "a") value, not the cell address, and using that in the range statement. It is all too prissy anyway, not needed.

I am sure what you are doing can be achieved much more simply, but try this correction in the code


Private Sub cmdRemove_Click()
Dim cellRange, myCell, fooCell As Range
Dim strChecker As String

With ThisWorkbook.Sheets(1)
Set cellRange = .Range("a2", .Cells(Rows.Count, "a").End(xlUp))
Set myCell = cellRange.Find(Me.cmbSchedName.Value, MatchCase:=True)
If Me.cmbSchedName.Value = "" Then
MsgBox ("Please select a schedule.")
Else
If myCell Is Nothing Then
MsgBox ("The selected schedule has been already deleted!")
Else
MsgBox ("Schedule " & Me.cmbSchedName.Value & " has been deleted!")
If chkStatic.Value = True Then
.Range(myCell, .Cells(myCell.Row, "I")).Delete
Else
.Range(.Cells(myCell.Row, "B"), .Cells(myCell.Row, "I")).Delete
strChecker = Left(Me.cmbSchedName.Value, 4)
For Each fooCell In cellRange
If Left(fooCell.Text, 4) = strChecker And _
Left(.Range("A" & fooCell.Row + 1).Text, 4) <> strChecker Then
Set myCell = fooCell
End If
Next fooCell
myCell.Delete
fillScheds
End If
End If
End If
End With
End Sub

malik641
10-08-2005, 08:17 AM
The second part of that statement is correct, but you can use variants without any problem because the data type is cast as Variant/Object/Range as soon as they are set.Huh...I could've swore I had a problem trying to set variant to a range, but after a little testing I realize that I'm mistaken. Thanks for the correction xld http://vbaexpress.com/forum/images/smilies/023.gif Good tip to remember.