Delete Rows with Indeterminate First Row

C

cecil23

I have a worksheet that may use up to 60000 rows. Many rows are blan
or have N/A in one or more columns. I then sort it, so that the usefu
info runs from the row 1 down to somewhere between 4000 to 10000 rows.
Before I try to import into Access, I want to run a macro from anothe
sheet in the workbook that deletes all rows below the useful info.
can find that row simply enough by doing a match on "N/A" in Col A.
save the results of that formula as a range name, say StartRowDel.
Say, in this set of data, the first N/A is on row 4561, so the macr
should delete from row 4561 to 60000.

What's the VBA code for that? (Seems this should be simple... gues
it's time to learn VBA...)

Thanks for the help
 
D

Dave Peterson

If I were doing this manually, I'd select A65536 and do a Find.

I'd look for N/A after the activecell--which means that it will find the topmost
N/A. Then if it finds it, just delete everything from there down.

As a macro:

Option Explicit
Sub testme()
Dim wks As Worksheet
Dim FoundCell As Range

Set wks = Worksheets("sheet1")

With wks
With .Range("a:a")
Set FoundCell = .Cells.Find(what:="n/a", _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, lookat:=xlWhole, _
MatchCase:=False)
End With

If FoundCell Is Nothing Then
MsgBox "N/A not found"
Else
.Range(FoundCell, .Cells(.Rows.Count, "A")).EntireRow.Delete
End If
End With

End Sub

I really looked for the characters N/A in the whole cell. Is that what you
meant?
 
Top