Deleting rows not containing value

[blondie7228]

I am trying to delete all rows until the macro reaches the row that contains "Loan Detail" in column A. I am using the following code, it seems to find the row but deletes it. I want it to delete UNTIL reaching that row.

Any input?


Sub DeleteRow()
Dim F
Dim MyValue
MyValue = "Loan Detail"
With Cells Set F = .Find(What:=MyValue, After:=.Cells(1, 1), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
If ((Not F Is Nothing) And (F.Row <> 1)) Then
Range(Range("A1"), Cells(F.Row - 1, 1)).EntireRow.Delete
If (F.Column <> 1) Then
Range(Range("A1"), Cells(1, F.Column - 1)).EntireColumn.Delete
End If
End If
End With
ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

 

[vaskov17]

your code works for me, except the last line which gives an error

 

[Michael M]

this works for me

Sub DeleteRow()
Dim F
Dim MyValue
MyValue = "Loan Detail"
With Cells
Set F = .Find(What:=MyValue, After:=.Cells(1, 1), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
If ((Not F Is Nothing) And (F.Row <> 1)) Then
Range(Range("A1"), Cells(F.Row - 1, 1)).EntireRow.Delete
End If
End With
End Sub

 

[blondie7228]

Thanks, I deleted the "ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks).EntireRow.Delete" and it ran great.

One more question, can I use multiple values for "My Value"? Ideally, I would like to use an or statement, something like My Value= "Property Value" OR "Property Financials" OR "Loan Detail" but I am not sure the proper way to code that.

 

[Michael M]

Blondie
I can see your list getting bigger as your work progresses !
So, you could use an Input Box to decide which text you want to Find

Sub DeleteRow()
Dim F, MV1
MV1 = InputBox("What Text Do you want to Find ?")
If MV1 = False Then Exit Sub
With Cells
Set F = .Find(What:=MV1, After:=.Cells(1, 1), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
If ((Not F Is Nothing) And (F.Row <> 1)) Then
Range(Range("A1"), Cells(F.Row - 1, 1)).EntireRow.Delete
End If
End With
End Sub

 

[Michael M]

OR
You make a list of "names to be found", and use almost the same code to pick the value from the list

Sub DeleteRow()
Dim F, MV1
MV1 = Application.InputBox("What Text Do you want to Find ?", Type:=8)
If MV1 = False Then Exit Sub
With Cells
Set F = .Find(What:=MV1, After:=.Cells(1, 1), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
If ((Not F Is Nothing) And (F.Row <> 1)) Then
Range(Range("A1"), Cells(F.Row - 1, 1)).EntireRow.Delete
End If
End With
End Sub