get address like A1 from column number

Posted on 2012-08-29
Last Modified: 2012-08-29
Dear experts,

I'm writing a function in excel which returns the last and first cell of my range. I seperated in column and row because the last cell wasn't always the correct one.(i.e. blank cells in last row so it doesn't return the appropriate range) now i was trying to make a function that will return the range as a product of lastcolumn and lastrow but i get a numeric value for the column and want to return the letter of the column( like column A, column B etcetera) this is my code till now
Function FindRangeRowColumn(UsedRng As Range) As Range

    Dim Firstrow As Long, lastrow As Long, firstCol As Long, LastCol As Long, strFirstCell As String, strLastCell As String
    Set UsedRng = ActiveSheet.UsedRange
    Firstrow = UsedRng(1).Row
    firstCol = UsedRng(1).Column
    lastrow = UsedRng(UsedRng.Cells.Count).Row
    LastCol = UsedRng(UsedRng.Cells.Count).Column

    'returns column name instead of column letter
    strFirstCell = Cells(Firstrow, firstCol)
    'returns column name instead of column letter
    strLastCell = Cells(lastrow, LastCol)

Open in new window

Thanks in advance:)

Question by:MarkVrenken
    1 Comment
    LVL 1

    Accepted Solution

    i think i got my answer.
    strfirstcell = cells(firstrow, firstCol).address(False,False)

    Open in new window


    Featured Post

    Courses: Start Training Online With Pros, Today

    Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

    Join & Write a Comment

    Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

    755 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

    19 Experts available now in Live!

    Get 1:1 Help Now