none
Select the Range of cells until the last not empty cell in Column RRS feed

  • Question

  • Hi,

    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

    Thursday, November 5, 2015 3:32 PM

Answers

  • (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
    • Marked as answer by GyTasS Wednesday, November 18, 2015 2:49 PM
    Friday, November 6, 2015 3:21 PM