none
Excel 2007 Select Visible columns in range RRS feed

  • Question

  • Hello,

    This should be an easy one - but somehow not for me.

    What code can I use to copy all rows for only the visible columns - maintaining cell and row formatting (hidden/row height, etc).

    Would there be any particular code that is better suited for pasting this back into the 'flat' file than the code below?

     

    BACKGROUND:

    I run a weekly process in on several large variable labor models.  Because each model is massive, we copy and paste several tabs from each model into a 'flat' file (values and formats). 

    Several of these tabs use only about 400 rows, and about 150 columns.  Some rows are hidden, but most columns are.  Of the 150 columns, only 15 are visible at any given time.

    These are weekly models, so there are new columns for each week, columns for month to date, columns for quarter to date, and columns for year to date.

    The prior owner of this process created a simple VBA code to copy and paste between the 'flat' file and the 'live' models - but it copies every column on every tab.  The 'flat' file only needs the visible columns.

    It can take 15 minutes for the code to complete its copy/ paste values/ paste formats function.  15 columns and 500 rows per worksheet shouldn't take 15 minutes.

    What code can I use to copy all rows for only the visible columns - maintaining row formatting (hidden/row height, etc).

    ex. The file/worksheet in front of me has ~400 rows, some hidden, some with odd row heights (to fit for printing) and visible columns A, AM, BC, CO, DE, DQ, ED, EH, EM, EO, ER, ET, EU, EV.  EV terminates the range, so it is effectively A1:EV400.

     

    CODE:

    Sub Update351()
    
    ThisFile = "351 Var Labor 2011 Actuals.xls"
    ThisNumber = 351
    ThisDC = "TP"
    
    Answer351 = MsgBox("Is the file open?", vbYesNo, "351 File Open")
    If Answer351 = vbNo Then Workbooks.Open fileName:="\\gsiccorp.net\gsiroot\Louisville\KY Public\Ops and Finance Reporting\1 Reporting 2011\Weekly Reporting\" & ThisFile, UpdateLinks:=False
    
       
       Windows(ThisFile).Activate
       Sheets("Weekly Status Reporting - " & ThisNumber).Select
       Cells.Select
       Selection.Copy
        ThisWorkbook.Activate
        Sheets("Weekly Status Reporting - " & ThisNumber).Select
        Cells.Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        
        Windows(ThisFile).Activate
        Sheets("Partner UPH Reporting " & ThisNumber).Select
        Cells.Select
        Application.CutCopyMode = False
        Selection.Copy
        ThisWorkbook.Activate
        Sheets("Partner UPH Reporting " & ThisNumber).Select
        Cells.Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        
        Windows(ThisFile).Activate
        Sheets("Partner Shipped Unit Report " & ThisNumber).Select
        Cells.Select
        Application.CutCopyMode = False
        Selection.Copy
        ThisWorkbook.Activate
        Sheets("Partner Shipped Unit Report " & ThisNumber).Select
        Cells.Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
            
        Windows(ThisFile).Activate
        Sheets(ThisDC & " Snr Mgt Trend Actual").Select
        Cells.Select
        Application.CutCopyMode = False
        Selection.Copy
        ThisWorkbook.Activate
        Sheets(ThisDC & " Snr Mgt Trend Actual").Select
        Cells.Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        
        Windows(ThisFile).Activate
        Sheets(ThisDC & " Snr Mgt Trend Plan").Select
        Cells.Select
        Application.CutCopyMode = False
        Selection.Copy
        ThisWorkbook.Activate
        Sheets(ThisDC & " Snr Mgt Trend Plan").Select
        Cells.Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    Calculate
        If Answer351 = vbNo Then Windows(ThisFile).Close savechanges:=False
        End Sub


     

    Monday, September 26, 2011 11:13 PM

Answers

  • You can copy all cells or all visible cells. You can't copy visible cells excluding hidden columns but include hidden rows.

    You could copy all visible cells with no hidden rows, then delete the rows you don't want.

    To copy visible cells you first need to select only visible cells:

    1. Select all data
    2. F5 Goto
    3. Click the Special button
    4. Check Visible cells only option
    5. OK
    6. Copy

    Record a macro of you doing this in Excel to get the VBA code


    Rod Gill

    The one and only Project VBA Book Rod Gill Project Management
    • Marked as answer by Bubbers66 Saturday, October 1, 2011 12:27 AM
    Tuesday, September 27, 2011 2:24 AM