Get current cell address

What is the code to return the address of the current cell ?


Problem:
I am running through a loop looking for a value so I don't know what row I am on when I hit that value.
When I hit it, I need to know the row number and how to use that number to reference another cell.


Poor code example of what I am after:

rowlocation = get.current.cell.row

activate.cell("A "& rowlocation &" ")


Thanks for any tips....
Sorry if it is a simple question, second day of learning VB for excel...

Hi duane thank you for the quick response...
Being new I am still confused.

What I am trying to do

Delete the rows I mark.
I am marking some cells in a column with a Y.
Running a loop down a column until I hit a Y.
Deleting the row.
Then deleting the same row on sheet2.
continuing the loop until done.


The test code I was using:

If IsEmpty(ActiveCell) = False Then
Rows("21:21").Select
Selection.ClearContents
End If
Sheets("Sheet2").Select
Rows("21:21").Select
Selection.ClearContents

The new code I tried:

If IsEmpty(ActiveCell) = False Then
thisrow = rng.Row
cells(thisrow,1).select
Selection.ClearContents
End If
Sheets("Sheet2").Select
cells(thisrow,1).select
Selection.ClearContents

Gave error on thisrow = rng.Row

Any ideas ?

Classic problem.

When deleting rows, it is best to start from last row and work up. For example:


Dim lastRow as Long

lastRow = 'however you determine last row

For i = lastRow to 1 Step -1
If "however you determine to delete the row" Then
Rows(i).Delete
End if
Next i

Thanks MSP77079

Dim lastRow as Long

lastRow = 'however you determine last row

For i = lastRow to 1 Step -1
If "however you determine to delete the row" Then
Rows(i).Delete
End if
Next i

Sweet...

This sample to defined cell's address:

Please Login or Register  to view this content.

Hey thanks guys....

With all your code I shouldn't have a problem now and have learned new ways of doing things.

Thankyou all.