• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 570
  • Last Modified:

get address like A1 from column number

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:)

Cheers
0
MarkVrenken
Asked:
MarkVrenken
1 Solution
 
MarkVrenkenAuthor Commented:
i think i got my answer.
strfirstcell = cells(firstrow, firstCol).address(False,False)

Open in new window


greets
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now