Extract the current row number when the whole row is selected

**Briguy2k** · 06-25-2009, 04:46 PM

I know I'm going to smack my forehead when someone points out how easy it is, but essentially, I'm looking for the opposite of this:

ActiveCell.Row

Here, when a cell is selected, I use this to get the number of the row that it is in. As you can see below, this code searches for a particular field, then deletes that whole row and adds 2 blank rows in it's place. I end up with a row selected and want to get that row number, go to cell(currentrownumber, "F") and do an autosum. But I can't find the specific function to do this. Any help please? VERY much appreciated.

    Dim rngFind As Range
    Dim currentrow As Integer
    Dim currentcol As Integer
    currentrow = ActiveCell.Row
    currentcol = ActiveCell.Column
    With Range(Selection, Selection.End(xlDown))
        Do
            Set rngFind = .Find(What:="#Item Number", After:=.Cells(1, 1), LookIn:=xlFormulas, _
                                LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                                MatchCase:=False, SearchFormat:=False)

                If rngFind Is Nothing Then Exit Do
              
            rngFind.EntireRow.Select
            Selection.Delete Shift:=x1up
            Selection.Insert Shift:=xlDown
            Selection.Insert Shift:=xlDown
'           MAGIC CODE
'           Cells(MAGICCODE, "F").Select
'           Application.SendKeys ("%=~")

            
        Loop
    End With

[/QUOTE]

**Richard Schollar** · 06-25-2009, 04:56 PM

What are you using the SendKeys for?

I think you can do away with a lot of your Selecting. You want to select the F column cell of the topmost blank row that you have just inserted, correct?

        Do
            Set rngFind = .Find(What:="#Item Number", After:=.Cells(1, 1), LookIn:=xlFormulas, _
                                LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                                MatchCase:=False, SearchFormat:=False)

                If rngFind Is Nothing Then Exit Do
              
            rngFind.EntireRow.ClearContents
            rngFind.EntireRow.Insert
         Cells(Activecell.Offset(-1).Row, "F").Select
           Application.SendKeys ("%=~")

            
        Loop

**Briguy2k** · 06-25-2009, 05:11 PM

The sendkeys is for the auto sum function to work as if I were hitting the button. It will guess the data to sum based on the grouping and hit enter. I got that from the microsoft site somewhere I think..

and yes exactly, that's what I'm trying to do, but your code didn't work. It broke on

Cells(Activecell.Offset(-1).Row, "F").Select

and said "error 1004 Application-defined or object-defined error." Any other ideas?

**DonkeyOte** · 06-25-2009, 06:58 PM

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

**Briguy2k** · 06-29-2009, 06:44 PM

In my haste to get ideas I forgot to do that, this post is now linked over there as well as that forums' post here: http://www.mrexcel.com/forum/showthread.php?t=398874