none
How could I get most right cell and most down cell address of worksheet including"cell format" by VBA in Excel 2007 ? RRS feed

  • Question

  • How could I get most right cell and most down cell address of worksheet including"cell format" by VBA in Excel 2007 ?
    Tuesday, April 21, 2015 1:08 AM

Answers

  • Hi,

    For your requirement, you could use Range.End property and Range.DisplayFormat property.

    Simple code:

    Sub LastRowInOneColumn()
    'Find the last used row in a Column: column A in this example
        Dim LastRow As Long
        With ActiveSheet
            LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        End With
        MsgBox LastRow
    End Sub
    Sub LastColumnInOneRow()
    'Find the last used column in a Row: row 1 in this example
        Dim LastCol As Integer
        With ActiveSheet
            LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
        End With
        MsgBox LastCol
    End Sub

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, April 20, 2015 7:52 AM
    Moderator
  • Try the following. Run the sub Test and it calls the User Defined Function.

    Note that it will not work as a User Defined Function on a worksheet; only when called from another sub because the Find function cannot be used in a UDF called from a worksheet.

    Because there is a myriad of formats if the type of format you require to be returned is not included and you cannot work out how to code it then please get back to me and tell me what type of formats you want to identify.

    Chr(9) in the MsgBox is a tab and vbCrLf is a line feed.

    Sub Test()
        Dim wsSht As Worksheet
        Dim rng As Range
       
        Set wsSht = Worksheets("Sheet1")
       
        Set rng = LastCell(wsSht, True)    'Last cell down (True parameter)
       
        If Not rng Is Nothing Then
            MsgBox "Last cell down:" & vbCrLf & _
                    "Address = " & Chr(9) & rng.Address(0, 0) & vbCrLf & _
                    "Font color = " & Chr(9) & rng.Font.Color & vbCrLf & _
                    "Font bold = " & Chr(9) & rng.Font.Bold & vbCrLf & _
                    "NumberFormat = " & Chr(9) & rng.NumberFormat
        End If
       
        Set rng = LastCell(wsSht, False)    'Last cell across (False parameter)
       
        If Not rng Is Nothing Then
            MsgBox "Last cell down:" & vbCrLf & _
                    "Address = " & Chr(9) & rng.Address(0, 0) & vbCrLf & _
                    "Font color = " & Chr(9) & rng.Font.Color & vbCrLf & _
                    "Font bold = " & Chr(9) & rng.Font.Bold & vbCrLf & _
                    "NumberFormat = " & Chr(9) & rng.NumberFormat
        End If

     End Sub

    Function LastCell(ws As Worksheet, DownAcross As Boolean) As Range
        'Finds the last used cell down or across in a worksheet
        'First parameter is Worksheet
        'Second parameter is True for Last down or False for last across
        Dim lngSearchOrder As Long
      
        If DownAcross Then
            lngSearchOrder = xlByRows
        Else
            lngSearchOrder = xlByColumns
        End If
       
        With ws
            Set LastCell = ws.Cells.Find(What:="*", _
                    LookIn:=xlFormulas, _
                    LookAt:=xlPart, _
                    SearchOrder:=lngSearchOrder, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False)
        End With
       
    End Function


    Regards, OssieMac

    Tuesday, April 21, 2015 2:07 AM

All replies

  • How could I get most right cell and most down cell address of worksheet including"cell format" by VBA in Excel 2007 ?
    Monday, April 20, 2015 12:32 AM
  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the MSDN forum for Excel

    http://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev&filter=alltypes&sort=lastpostdesc

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    George Zhao
    TechNet Community Support


    It's recommended to download and install Configuration Analyzer Tool (OffCAT), which is developed by Microsoft Support teams. Once the tool is installed, you can run it at any time to scan for hundreds of known issues in Office programs.

    Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.
    Monday, April 20, 2015 1:37 AM
  • Re: last cell (kinda)

    This should work most of the time...
    Sub test()
      Dim Num As Long
      Dim rng As Range

      Set rng = ActiveSheet.UsedRange
      Num = rng.Cells.Count
      MsgBox rng(Num).Address
    End Sub

    '---

    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Edited by James Cone Tuesday, November 1, 2016 12:40 AM
    Monday, April 20, 2015 4:37 AM
  • Hi,

    For your requirement, you could use Range.End property and Range.DisplayFormat property.

    Simple code:

    Sub LastRowInOneColumn()
    'Find the last used row in a Column: column A in this example
        Dim LastRow As Long
        With ActiveSheet
            LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        End With
        MsgBox LastRow
    End Sub
    Sub LastColumnInOneRow()
    'Find the last used column in a Row: row 1 in this example
        Dim LastCol As Integer
        With ActiveSheet
            LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
        End With
        MsgBox LastCol
    End Sub

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, April 20, 2015 7:52 AM
    Moderator
  • Try the following. Run the sub Test and it calls the User Defined Function.

    Note that it will not work as a User Defined Function on a worksheet; only when called from another sub because the Find function cannot be used in a UDF called from a worksheet.

    Because there is a myriad of formats if the type of format you require to be returned is not included and you cannot work out how to code it then please get back to me and tell me what type of formats you want to identify.

    Chr(9) in the MsgBox is a tab and vbCrLf is a line feed.

    Sub Test()
        Dim wsSht As Worksheet
        Dim rng As Range
       
        Set wsSht = Worksheets("Sheet1")
       
        Set rng = LastCell(wsSht, True)    'Last cell down (True parameter)
       
        If Not rng Is Nothing Then
            MsgBox "Last cell down:" & vbCrLf & _
                    "Address = " & Chr(9) & rng.Address(0, 0) & vbCrLf & _
                    "Font color = " & Chr(9) & rng.Font.Color & vbCrLf & _
                    "Font bold = " & Chr(9) & rng.Font.Bold & vbCrLf & _
                    "NumberFormat = " & Chr(9) & rng.NumberFormat
        End If
       
        Set rng = LastCell(wsSht, False)    'Last cell across (False parameter)
       
        If Not rng Is Nothing Then
            MsgBox "Last cell down:" & vbCrLf & _
                    "Address = " & Chr(9) & rng.Address(0, 0) & vbCrLf & _
                    "Font color = " & Chr(9) & rng.Font.Color & vbCrLf & _
                    "Font bold = " & Chr(9) & rng.Font.Bold & vbCrLf & _
                    "NumberFormat = " & Chr(9) & rng.NumberFormat
        End If

     End Sub

    Function LastCell(ws As Worksheet, DownAcross As Boolean) As Range
        'Finds the last used cell down or across in a worksheet
        'First parameter is Worksheet
        'Second parameter is True for Last down or False for last across
        Dim lngSearchOrder As Long
      
        If DownAcross Then
            lngSearchOrder = xlByRows
        Else
            lngSearchOrder = xlByColumns
        End If
       
        With ws
            Set LastCell = ws.Cells.Find(What:="*", _
                    LookIn:=xlFormulas, _
                    LookAt:=xlPart, _
                    SearchOrder:=lngSearchOrder, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False)
        End With
       
    End Function


    Regards, OssieMac

    Tuesday, April 21, 2015 2:07 AM