Excel VBA - Search/Select/Delete Range Based on Next Non-Blank Cell in Column

0
votes

I'm hoping someone will be able to help me out with this.

My intent is to have the worksheet search for a name and find it. I've got that done without issues with the following code:

Sub Removemember_Click()
MemberName = InputBox(Prompt:="Enter Member's Name")
Cells.Find(What:=MemberName, _
After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
End Sub

What I need to do next is search downward in the "C" column until I find a non-blank cell, then select all rows between the searched-for name and the next name (though not including the next name). Then I need to delete (remove) the rows. Can anyone lend me a hand?

1
vbaexcel
"I've got that done without issues" - wait till the .Find call doesn't find what it's looking for. Boom, error 91. Don't call members on objects that may or may not be set to a valid reference. When Range.Find returns Nothing the .Activate call will blow up. Note: You probably don't want to activate it anyway. - Mathieu Guindon
Yes, the code returns an error when an invalid name is entered, though I'm not overly concerned with it as it doesn't break my sheet at the moment. I'm certain there are many better ways to go about it - everything I know about VBA is self-taught and I'm an amateur at best. - EHemsing

1 Answers

0
votes

The code below provides one way. Once you understand it, modify as needed and remove the .select statements.

enter image description here

Option Explicit
Sub Removemember_Click()
Dim memberName As String
Dim startR As Range, endR As Range, rangeToDelete As Range
Set startR = ActiveSheet.Range("C1")
startR.Activate
memberName = InputBox(Prompt:="Enter Member's Name")
Set startR = Cells.Find(What:=memberName, _
After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False)
Set endR = startR.End(xlDown).Offset(-1, 0)
startR.Select
endR.Select
Set rangeToDelete = Rows(startR.Offset(1, 0).Row & ":" & endR.Row)
rangeToDelete.Select
rangeToDelete.Delete
End Sub