How to select a specific row range in an Excel table column RRS feed

  • Question

  • Excel 2010 table code operations are sparsely documented with examples. Column operations are straight forward because columns are identified by column headers, but row operations are a bit mysterious. I have an Excel table, e.g. 'tblHistorical' (defined in the Name Manager), with column names in the header row and  row names in the first table column. I need a VBA code example for selecting a specific row range, e.g. from 'Day7' to 'Day14', in a specific column, e.g. 'APA', which can vary.

    Thanks in advance.

    Thursday, August 16, 2012 2:30 AM


  • You could apply a filter and then assign the visible cells to a range variable as follows.

    Sub Macro1()
        Dim strCrit1 As String
        Dim strCrit2 As String
        Dim rngFiltered As Range
        Dim cel As Range
        strCrit1 = ">=Day07"
        strCrit2 = "<=Day14"
        ActiveSheet.ListObjects("tblHistorical").Range.AutoFilter Field:=1, _
            Criteria1:=strCrit1, Operator:=xlAnd, Criteria2:=strCrit2
        Set rngFiltered = ActiveSheet.Range("tblHistorical[Column1]").SpecialCells(xlCellTypeVisible)
        ActiveSheet.ListObjects("tblHistorical").Range.AutoFilter Field:=1    'Turn off the filter
        'Following for testing only
        For Each cel In rngFiltered
            MsgBox cel.Value
        Next cel
    End Sub

    Regards, OssieMac

    Thursday, August 16, 2012 5:05 AM