Find current row number and select row

[JohnM]

I am very new to Excel VB and I need some help. With some other macros, I end
up with several rows with "N/A" in some of the colums. I am able to sort the
spreadsheet and get the rows to end up at the top of the spreadsheet, then I
am trying to delete those rows. So I need to find the row number that has
data, then move up one row and delete the rows from there to one from the
top. Below is what I have so far.

Sub DeleteBlankNARecords()
Dim RowNum As Integer
Sheets("PlDetails").Select
Cells.Select
ActiveWorkbook.Worksheets("PlDetails").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("PlDetails").AutoFilter.Sort.SortFields.Add
Key:= _
Range("$D$1:$D$896"), SortOn:=xlSortOnValues, Order:=xlDescending,
DataOption _
:=xlSortNormal
With ActiveWorkbook.Worksheets("PlDetails").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1").Select
Selection.End(xlDown).Select
' set row number to current row
Rows("RowNum:RowNum").Select
Range(Selection, Selection.End(xlUp)).Select
Rows("2:RowNum-1").Select
Range("ARowNum").Activate
Selection.ClearContents
Selection.Delete Shift:=xlUp
Range("A2").Select
End Sub

Thanks

 

[Jacob Skaria]

Replace the below lines with

Selection.End(xlDown).Select
' set row number to current row
Rows("RowNum:RowNum").Select

RowNum = Range("A1").End(xlDown).Row
Rows(RowNum).Select


If this post helps click Yes

 

[p45cal]

You may not need to do any sorting, try this on an experimenta
worksheet:
Sub DeleteBlankNARecords()
Dim RowNum As Integer
Sheets("PlDetails").Select
For RowNum = 896 To 2 Step -1
If Application.WorksheetFunction.IsNA(Cells(RowNum, "D")) The
Rows(RowNum).Delete
'perhaps change the line above to whatever is in column D that yo
want to delete eg.:
'If Cells(RowNum, "D").Value = "N/A" Or Cells(RowNum, "D").Value = "
Then Rows(RowNum).Delete
'you get the drift
Next RowNum
End Su

 

[JohnM]

This worked for me.

Thanks!