none
How to paste skip hidden column?

    Question

  • Hello,

    I'm live in Viet Nam. I have problem with paste data in Excel 2010. I have data in 3 colum A, B, C (value: 1, 2, 3). Now, I want copy and paste to arangedifferent have a column hidden (in example is colum G hidden). But, when I paste is data paste to colum G (hidden). I don't want, I want data paste to F, H, I (value:1, 2, 3).

    Please, help me.

    Email: minhphuong.humg@gmail.com

    Thank you very much.

    Sunday, September 15, 2013 2:54 AM

Answers

  • That is not possible with the usual Excel methods, you have to write a macro that skips hidden columns. See the example below.

    Andreas.

    PS.: I'm on vacation the next 3 weeks, an answer may take a while.

    Option Explicit

    Sub Test()
      CopySkipHiddenColumns Range("A1:C1"), Range("F1")
    End Sub

    Function CopySkipHiddenColumns(ByVal Source As Range, ByVal Dest As Range) As Boolean
      'Copies Source to Dest, skips hidden columns in Dest, true if success
      Dim C As Range
      'Exit if an error occurs
      On Error GoTo ExitPoint
      'Be sure Dest is only one cell
      Set Dest = Dest(1, 1)
      'Visit each column
      For Each C In Source.Columns
        'Is Dest hidden?
        Do While Dest.EntireColumn.Hidden
          'End of the sheet reached?
          If Dest.Column = Dest.Parent.Columns.Count Then Exit Function
          'Shift Dest to the right
          Set Dest = Dest.Offset(, 1)
        Loop
        'Copy this column
        C.Copy Dest
        'Shift Dest to the right
        Set Dest = Dest.Offset(, 1)
      Next
      'Success
      CopySkipHiddenColumns = True
    ExitPoint:
    End Function

    Sunday, September 15, 2013 8:02 AM

All replies

  • That is not possible with the usual Excel methods, you have to write a macro that skips hidden columns. See the example below.

    Andreas.

    PS.: I'm on vacation the next 3 weeks, an answer may take a while.

    Option Explicit

    Sub Test()
      CopySkipHiddenColumns Range("A1:C1"), Range("F1")
    End Sub

    Function CopySkipHiddenColumns(ByVal Source As Range, ByVal Dest As Range) As Boolean
      'Copies Source to Dest, skips hidden columns in Dest, true if success
      Dim C As Range
      'Exit if an error occurs
      On Error GoTo ExitPoint
      'Be sure Dest is only one cell
      Set Dest = Dest(1, 1)
      'Visit each column
      For Each C In Source.Columns
        'Is Dest hidden?
        Do While Dest.EntireColumn.Hidden
          'End of the sheet reached?
          If Dest.Column = Dest.Parent.Columns.Count Then Exit Function
          'Shift Dest to the right
          Set Dest = Dest.Offset(, 1)
        Loop
        'Copy this column
        C.Copy Dest
        'Shift Dest to the right
        Set Dest = Dest.Offset(, 1)
      Next
      'Success
      CopySkipHiddenColumns = True
    ExitPoint:
    End Function

    Sunday, September 15, 2013 8:02 AM
  • Thank for you,

    However, I want a option a range different how i do?

    Ex:

    When run macro, a option question: Range copy and Range paste.

    Please.


    • Edited by DoPhuong Monday, October 14, 2013 3:21 PM
    Monday, October 14, 2013 3:18 PM
  • You can use SpecialCells to get the visible only, here is an example how to select the visible cells in the current selection only.

    Andreas.

    Sub ExcludeHiddenCellsFromSelection()
      'Select the visible cells only
      On Error Resume Next
      Selection.SpecialCells(xlCellTypeVisible).Select
    End Sub

    Tuesday, October 15, 2013 10:26 AM