none
Excel VBA select range dynamically RRS feed

  • Question

  • Hi All,

    I want to select range of cells based on following excel sample data

    Excel sample

    A                                           B                                        C                                    D

    aa                                         bb                                                                           dd

                                                 mm                                      oo

    uu                                                                                    ii                                     rr

    ll                                               aa                                                                         

                                                                                           tt                                    dd

    1. How shall i select range(A1:C5) dynamically without mentioning row number and colomn number. I want to select from A1 till last non empty cell row in columnA TO C1 and last non empty cell row in column c. There may be blanks in column A & C
    2. How shall i select range (A2 to A4 and  rows (2,3 & 4) to right of column A till column that has data. So in above example range (A2:D4) shall be selected
    3. How shall i select range B2 to last non empty cell in column C. In above excel sample B2:C5 shall be selected

    Thanks,

    Zaveri


    • Edited by zaveri cc Thursday, September 4, 2014 4:20 PM
    Thursday, September 4, 2014 4:08 PM

Answers

  • Hi,

    In Excel Object Model, Range.End Property returns a Range object that represents the cell at the end of the region that contains the source range. You can use it to pick out the last non empty cell in a column or a row.

    If you want to find the last non-empty cell in column C, you can refer to the code below.

    ActiveSheet.Range("C" & ActiveSheet.Rows.Count).End(xlUp).Select

    Then you can get its row number and column number by

    Dim col As Long
    Dim row As Long
    col= ActiveSheet.Range("C" & ActiveSheet.Rows.Count).End(xlUp).Column
    row = ActiveSheet.Range("C" & ActiveSheet.Rows.Count).End(xlUp).Row

    After getting the row and column number of the last non empty cell, you can select range from any specific cell to the last non empty cells as you want.

    'select range B2 to last non empty cell in column C
    ActiveSheet.Range(Cells(2, 2), Cells(row, col)).Select

    In the same way, if you want to get the last non empty cell in row 1, you can refer to the link below.

    'get last non empty cell in row 1
    ActiveSheet.Cells(1, ActiveSheet.Columns.Count).End(xlToLeft).Select


    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.

    Friday, September 5, 2014 5:24 AM
    Moderator
  • Here is a little sample how a programmer would get a part of the data.

    Andreas.

    Sub Test()
      Dim R As Range, F As Range, T As Range
      
      Set R = Range("A1").CurrentRegion
      MsgBox R.Address(0, 0), , "All data"
      
      Set R = Range("A1").CurrentRegion
      Set R = Range(R.Rows(2), R.Rows(4))
      MsgBox R.Address(0, 0), , "Rows 2 to 4"
      
      Set R = Range("A1").CurrentRegion
      Set F = R.Find("mm", LookAt:=xlPart)
      Set T = R.Find("tt")
      Set R = Range(F, T)
      MsgBox R.Address(0, 0), , "From mm to tt"
      
      Set R = Intersect(Range(F, T).EntireRow, Range("A1").CurrentRegion)
      MsgBox R.Address(0, 0), , "From mm to tt in rows"
    End Sub
    

    Friday, September 5, 2014 10:32 AM

All replies

  • Hi,

    In Excel Object Model, Range.End Property returns a Range object that represents the cell at the end of the region that contains the source range. You can use it to pick out the last non empty cell in a column or a row.

    If you want to find the last non-empty cell in column C, you can refer to the code below.

    ActiveSheet.Range("C" & ActiveSheet.Rows.Count).End(xlUp).Select

    Then you can get its row number and column number by

    Dim col As Long
    Dim row As Long
    col= ActiveSheet.Range("C" & ActiveSheet.Rows.Count).End(xlUp).Column
    row = ActiveSheet.Range("C" & ActiveSheet.Rows.Count).End(xlUp).Row

    After getting the row and column number of the last non empty cell, you can select range from any specific cell to the last non empty cells as you want.

    'select range B2 to last non empty cell in column C
    ActiveSheet.Range(Cells(2, 2), Cells(row, col)).Select

    In the same way, if you want to get the last non empty cell in row 1, you can refer to the link below.

    'get last non empty cell in row 1
    ActiveSheet.Cells(1, ActiveSheet.Columns.Count).End(xlToLeft).Select


    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.

    Friday, September 5, 2014 5:24 AM
    Moderator
  • Here is a little sample how a programmer would get a part of the data.

    Andreas.

    Sub Test()
      Dim R As Range, F As Range, T As Range
      
      Set R = Range("A1").CurrentRegion
      MsgBox R.Address(0, 0), , "All data"
      
      Set R = Range("A1").CurrentRegion
      Set R = Range(R.Rows(2), R.Rows(4))
      MsgBox R.Address(0, 0), , "Rows 2 to 4"
      
      Set R = Range("A1").CurrentRegion
      Set F = R.Find("mm", LookAt:=xlPart)
      Set T = R.Find("tt")
      Set R = Range(F, T)
      MsgBox R.Address(0, 0), , "From mm to tt"
      
      Set R = Intersect(Range(F, T).EntireRow, Range("A1").CurrentRegion)
      MsgBox R.Address(0, 0), , "From mm to tt in rows"
    End Sub
    

    Friday, September 5, 2014 10:32 AM