Finding the Row number from a selected cell address

HunterN

Active Member
Joined
Mar 19, 2002
Messages
479
Hi,

I have the following code and I was wondering how to assign the row to
a variable to insert in the line Rows(4).EntireRow.Delete insted of the 4. I would like to do the below code without the selection.
I never know how to rewrite things.

Briefly, I am trying to find all the rows that have a 'D' in the ACTION column, and delete them.

NAME SIZE LOCATION ACTION
PESF1 2 191-192 N
PESG 2 193-194 D
PESZ 3 195-197 N
PESZZ 2 198-199 N

Sub Filter_Del_Out()

'* Find the rows that need to be deleted
DeleteVar = "D"
Set myFind = Columns(4).Find(what:=DeleteVar, _
After:=Range("D2"), _
LookIn:=xlValues, _
Searchorder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=True)

If Not myFind Is Nothing Then
myFind.Address

myFind.Select
findAddress = myFind.Address
delRow = Selection.Row

Rows(4).EntireRow.Delete
End If

End Sub

Thanks, Nancy
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,010
Office Version
  1. 365
How about:

Code:
Sub Filter_Del_Out()
Columns("D:D").AutoFilter Field:=1, Criteria1:="D"
Range("D2:D65536").SpecialCells(xlCellTypeVisible).EntireRow.Delete
Columns("D:D").AutoFilter
End Sub
 

HunterN

Active Member
Joined
Mar 19, 2002
Messages
479
OK, that was great!

I can't believe that I know so little!
How do I learn to do those things in such a simplified way? :oops:

Thank you so much for your help!

Nancy (y)
 

Forum statistics

Threads
1,137,060
Messages
5,679,372
Members
419,824
Latest member
Mercy kiara

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top