Cannot find Cell

[Kou Vang]

Sorry for this easy question, but the months of Access Programming has warped
my mind of the many things I remembered in Excel. If I want to find the word
"Date", what do I need to add to my VB. Thanks!

With ActiveSheet.Range("a1:a50")

Set C = .Find("Date", LookIn:=xlValues)

ActiveCell.EntireRow.FillUp.Select
Selection.Delete shift:=xlUp

Secondly, will the second part of my code delete all the rows above the
"Date" row?

 

[Dave Peterson]

One way:

Option Explicit
Sub testme01()

Dim RngToSearch As Range
Dim LookForWhat As String
Dim FoundCell As Range

LookForWhat = "Date"

With Worksheets("sheet1")
Set RngToSearch = .Range("a1:a50")
With RngToSearch
Set FoundCell = .Cells.Find(what:=LookForWhat, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
End With

If FoundCell Is Nothing Then
MsgBox "Not Found"
Else
If FoundCell.Row = 1 Then
MsgBox "Found in row 1--not deleted!"
Else
.Range("A1:A" & FoundCell.Row - 1).EntireRow.Delete
End If
End If
End With

End Sub

 

[Jim Thomlinson]

You need to ensure that Date was found and then select that cell for your
code to work...

Set C = .Find("Date", LookIn:=xlValues)
if C is nothing then
msgbox "Not Found"
else
C.select
'the rest of your code
endif

 

[Kou Vang]

OMG! Yes, how forgetful of me! Of course I was finding it in my code, but I
wasn't even selecting it, that is why it was being selected! Thanks Jim! (I
hate Mondays!)