locked
Select the Range of cells from ActiveCell to the last non empty cell in the same column as active cell RRS feed

  • Question

  • Can somebody help me to find the way for selecting the range of cells in a column that takes into account empty cells as well (selects the range from ActiveCell.Offset(1, 0) until the last not empty cell in a column)?

    I have defined the variable LastRow to define the number of rows. However, It could be so, that even LastRow variable gives the value equal to 0, because Column "A" does not contain any value...

    Can somebody suggest more robust and efficient way of defining the range an copying it?

    Thank you!

    Sub ArrangingColumns()
        
        Dim Sheet As Worksheet
        Dim LastRow As Long
        
        
        Application.ScreenUpdating = False
        
        Set Sheet = ActiveWorkbook.Worksheets("IOList")
        Sheet.Range("A1").Select
        
        LastRow = Sheet.Range("A" & Sheet.Rows.Count).End(xlUp).row
    
        
        Do Until ActiveCell.Value = ""
            If ActiveCell.Value = "Element ID" Then
                Sheet.Range(ActiveCell.Offset(1, 0), ActiveCell.End(xlDown)).Copy Sheet.Range("A2")
            End If
                ActiveCell.Offset(0, 1).Select
        Loop
    

    Friday, November 6, 2015 2:29 PM

All replies

  • (selects the range from ActiveCell.Offset(1, 0) until the last not empty cell in a column)?

    You don't need to select a range to work with it....

        Dim rngC As Range
        Set rngC = Range(ActiveCell.Offset(1, 0), Cells(Rows.Count, ActiveCell.Column).End(xlUp))
        MsgBox rngC.Address
    Friday, November 6, 2015 3:19 PM