Last column used range

  • Hello,


    I have an excel file that has data in a dynamic range, ranging from Column to column. I have some filters on column A. What I would like is to select the last used column, ranging from first visible cell in that column to the very last cell that has data in the filtered range. I hope its understandable.

  • You shouldn't need to select a range in VBA.


    This will give you the visible cells in the filtered range


    Code
    MsgBox ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Address
  • Hi,


    Actually I do need the selection part since the data will be copied and moved to a new workbook. And I want only the last used column (highlighted in yellow), which can be in any column since my data is dynamic both in columns and in rows. That's why I tried to use the last column and last row but I cant seem to make it work. I always end up selecting the last cell only, not the range.


  • You don't need to select a range to copy it.


    It looks like you might be using a Table, it's hard to tell without seeing an example workbook. If you are you can refer to the the table's DataBodyRange or if not use something like this

    Code
    Dim oWb As Workbook
    Dim oWs As Worksheet
    
    
    Set oWb = Workbooks("target workbook name")
    Set oWs = oWb.Sheets(1)
    
    Sheet1.AutoFilter.Range.Copy oWs.Range("A1")

    with an example workbook and more information like the workbook name to copy to, is the target workbook open, are you adding the copied data to existing data, I can provide better code

  • Its not an actual table, what I showed in the image was just a depiction of how the information is stored in a plain sheet. I cannot attach any files since they are sensitive information. Basically what I would like to achieve is to count the columns in Sheet1, and on the last one that has data, use a rows. Count downwards to the last non-blank row and then copy that range to the new Workbook. The move part I can manage, but I cannot achieve the range selection/copy. Also, my headers don't have the same information so I cannot use that as reference.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!