Find the row from cell reference (VBA)

Posted on 2005-04-06
Last Modified: 2011-09-20
I was to delete a row from cell reference.

I have created a macro that returns the address of  a cell eg. $A$15 in a variable.
With this address I was to created a code that deletes row 15.

more examples  $B$5  delete row 5
                        $c$20 delete row 20

I was thinking of using left(variable,2) but this doesnt work..

Thank you in advance.
Question by:Aiysha
    LVL 19

    Accepted Solution

    Option Compare Database

    Private Sub Command0_Click()
    MsgBox GetRow("$c$20")
    End Sub

    Function GetRow(ByVal CellAddress As String) As String
    Dim mySplit() As String
    mySplit = Split(CellAddress, "$", -1)
    GetRow = mySplit(UBound(mySplit))
    End Function

    LVL 19

    Expert Comment

    ps. I think its time to close those 11 open paqs, Aiysha. Some of them are more than a month old.

    LVL 50

    Assisted Solution

    by:Dave Brett
    Using the range object is probably easiest, see KillRange.

    Sub TestSub()
    KillRange ("$A$15")
    End Sub

    Sub KillRange(Addr As String)
    Dim myrange As Range
    Set myrange = Range(Addr)
    End Sub



    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
    This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now