Find current row number and select row
 

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

Find current row number and select row
 

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
---------------
Jacob Skaria


"JohnM" wrote:

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

Find current row number and select row
 

You may not need to do any sorting, try this on an experimental
worksheet:
Sub DeleteBlankNARecords()
Dim RowNum As Integer
Sheets("PlDetails").Select
For RowNum = 896 To 2 Step -1
If Application.WorksheetFunction.IsNA(Cells(RowNum, "D")) Then
Rows(RowNum).Delete
'perhaps change the line above to whatever is in column D that you
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 Sub


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=125286

Find current row number and select row
 

This worked for me.

Thanks!
--
JohnM


"p45cal" wrote:


You may not need to do any sorting, try this on an experimental
worksheet:
Sub DeleteBlankNARecords()
Dim RowNum As Integer
Sheets("PlDetails").Select
For RowNum = 896 To 2 Step -1
If Application.WorksheetFunction.IsNA(Cells(RowNum, "D")) Then
Rows(RowNum).Delete
'perhaps change the line above to whatever is in column D that you
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 Sub


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=125286