Range.AutoFilter Method(5) - 필터된 영역에 붙여넣기(1)

서울시 지역 시간별 수질 현황5.xlsm

0.02MB

 
 
 

1. 필터링된 경우에도 보이지 않는 셀까지 데이터가 붙여지는 문제점이 있음

 
위와 같은 데이터가 있을 경우 동명을 가락1동으로 필터링하면

가락1이라고 입력하고 엔터키를 누르면 가락1동으로 필터링

 
아래와 같이 가락1동 관련 데이터만 표시되는데, 

 
H25셀부터 H34셀까지의 내용을 복사한 후 H2셀에 붙여 넣으면

아래 부분의 데이터를 복사한 후 H2셀에 붙여넣기

 
10개를 복사했는데, H2셀부터 H9셀까지 6개만 붙여 넣어져서 4개는 사라졌습니다. 원인은 7행부터 8행까지와 10행부터 11행까지 숨겨진 행에도 붙여 넣어져서 그렇습니다.

H2셀부터 보이는 셀에만 붙여넣기가 되지 않고, 연속적으로 데이터가 붙여넣어짐

 
홈 탭 > 정렬 및 필터 > 지우기를 눌러 모든 데이터를 표시하면

필터 - 지우기 > 모든 데이터 보기

 
필터링된 경우에도 보이는 셀에만 복사되는 것이 아니라, 2행부터 11행까지 연속적으로 붙여 넣기가 됩니다.

연속적으로 데이터가 붙여 넣어졌음

 
그러나, 필터링된 데이터를 다른 곳에 붙여 넣는 것은 문제가 없습니다.
예를 들어 가락1동으로 필터링된 상태에서 6행과 9행을 선택한 후 43행에 붙여넣으면 2줄만 붙여넣기가 됩니다.

필터링된 데이터를 붙여넣는 것은 문제 없음

 

2. 필터링된 경우 보이는 셀에만 붙여넣기

 

가. 복사범위.Copy Destination:=붙일범위 (=> 안됨)

 
개발도구 - Visual Basic을 누른 다음 아래와 같이 코드를 작성하고, H2셀부터 H21셀까지 범위를 잡고 Delete키를 눌러 내용을 지운 다음 F5키를 눌러 매크로를 실행하면

Sub FilteredRange_Copy1()
    If Not ActiveSheet.FilterMode Then
        ActiveCell.AutoFilter field:=2, Criteria1:="가락1*"
    End If
    
    Range("h25:h34").Copy Destination:=Range("h2")
End Sub

 
위와 마찬가지로 H2셀부터 H9셀까지만 붙여 넣어집니다.

매크로를 작성한 후 실행해도 숨겨진 셀에 붙여넣어짐

 
필터를 지우기하면 아래와 같이 H2셀부터 H11셀까지 데이터가 채워졌습니다.

 

나. SpecialCells(xlCellTypeVisible)과 copy destinaton 이용(1) (=> 이것도 안됨)

복사할 범위는 문제가 없는데, 필터 된 셀, 다시 말해 화면에 보이는 셀에만 붙여 넣어져야 하는데 그렇지 못한 것이므로 붙여 넣을 범위를 SpecialCells(xlCellTypeVisible)를 이용해 지정하고 붙여 넣어도 위와 마찬가지로 화면에 보이는 셀에만 붙여 넣어지지 않습니다.
 
아래와 같이 매크로를 작성합니다.
 

Option Explicit

Dim CopyRange As Range, PasteRange As Range
Dim c As Range
Dim i As Integer

Sub FilteredRange_Copy1()
    If Not ActiveSheet.FilterMode Then
        ActiveCell.AutoFilter field:=2, Criteria1:="가락1*"
    End If
    
    Range("h25:h34").Copy Destination:=Range("h2")
End Sub

Sub FilteredRange_Copy2()
    Range("a2").Select
    If Not ActiveSheet.FilterMode Then Selection.AutoFilter 2, "가락1*"
    
    Set CopyRange = Application.InputBox("복사할 범위를 선택하세요.", Type:=8)
    Set PasteRange = Application.InputBox("붙여넣을 첫번째 셀을 선택하세요.", Type:=8)
    
    Set PasteRange = Range(PasteRange, PasteRange.End(xlDown)).SpecialCells(xlCellTypeVisible)
    MsgBox PasteRange.Address(0, 0)
    
    CopyRange.Copy Destination:=PasteRange
End Sub

 
(1) 매크로 설명
 
다음에 만들 매크로 문에도 사용할 공통 변수이기 때문에 Sub 문 안이 아니라 위에 선언했습니다.
 
Dim CopyRange As Range, PasteRange As Range : 복사할 범위는 CopyRange, 붙여 넣을 범위는 PasteRange이며, 범위 변수로 선언했습니다.

Dim c As Range : PasteRange내의 셀을 하나씩 돌아가면서 실행해야 하기 때문에 c란 범위 변수를 선언했습니다.
 
Dim i As Integer : CopyRange의 범위 내 셀을 배열로 선언하기 위한 인덱스 변수입니다.
 
Range("a2").Select : A2셀을 클릭합니다.

If Not ActiveSheet.FilterMode Then Selection.AutoFilter 2, "가락1*" : FilterMode, 다시 말해 필터의 조건까지 설정됐는지 확인해서 FilterMode가 False인 경우 두 번째 필드를 기준으로 "가락1*"이라고 지정해서 "가락1"로 시작하는 동명으로 데이터를 제한합니다.
 
Set CopyRange = Application.InputBox("복사할 범위를 선택하세요.", Type:=8)
Set PasteRange = Application.InputBox("붙여넣을 첫번째 셀을 선택하세요.", Type:=8)
 
Application.InputBox에 대한 설명은 https://lsw3210.tistory.com/entry/%EB%B2%94%EC%9C%84-%EB%93%B1%EC%9D%84-%EC%9E%85%EB%A0%A5%EB%B0%9B%EB%8A%94-ApplicationInputBox-%EB%A9%94%EC%86%8C%EB%93%9C1 을 참고 바랍니다.
 
위에서 Type이 8이므로 범위 지정을 받는 대화 상자를 표시하고, CopyRange 또는 PasteRange 범위 변수에 저장합니다.
 
Set PasteRange = Range(PasteRange, PasteRange.End(xlDown)).SpecialCells(xlCellTypeVisible)
PasteRange를 다시 PasteRange에서 맨 아래로 내려가서 걸리는 셀까지 범위를 정한 후 보이는 셀만으로 재지정합니다.
 
MsgBox PasteRange.Address(0, 0) : PasteRange의 셀 주소를 상대참조형식으로 메시지 상자에 표시합니다.
 
CopyRange.Copy Destination:=PasteRange : 복사할 영역을 붙여 넣을 영역에 붙여 넣습니다.
 
 
(2) 매크로 실행
 
VB 에디터에서 엑셀 모양의 "엑셀로 돌아가기" 아이콘을 눌러
 
개발도구 - 매크로에서 FilteredRange_Copy2를 선택한 후 실행하고

 
"복사할 범위를 선택하세요"라고 하면 H25셀부터 H34셀까지 선택하고 확인 버튼을 누르고,

복사할 범위 입력 대화상자

 
"붙여 넣을 첫번째 셀을 선택하세요"라고 하면 H2셀을 지정하고 확인 버튼을 누르면

붙여넣을 첫번째 셀 입력 대화상자

 
메시지 박스에 보이는 셀 H2셀에서 H6셀, 그리고 H9셀만 표시됩니다. H21셀까지 표시되지 않는 것은 연속된 데이터가 H9셀까지만 있어서 그렇습니다.

보이는 셀의 주소

 
여기서 확인 버튼을 누르면 런타임 오류가 발생하는데, 복사 영역과 붙여 넣을 영역의 크기가 달라서 그렇다고 합니다.

복사 영역과 붙여넣을 영역의 크기가 다름 -> 붙여 넣기 오류

 
따라서, H9셀의 채우기 핸들을 H21셀까지 끌어 데이터를 22.7로 채웁니다.

 
그리고, 매크로를 실행하면 PasteRange로 보이는 셀의 주소만 표시하는데 10개 맞습니다.

보이는 셀의 주소

 
그래도 확인 버튼을 누르면 위와 마찬가지로 복사 영역과 붙여 넣을 영역의 크기가 다르다는 에러가 발생합니다.
숨겨진 행도 범위로 생각하는 듯합니다.
 
 

다. SpecialCells(xlCellTypeVisible)과 copy destinaton 이용(2) (=> 성공)

따라서, PasteRange의 셀만 하나씩 선택해서 붙여 넣도록 매크롤 문을 수정해야 합니다.
 

Sub FilteredRange_Copy3()
    Range("a2").Select
    If Not ActiveSheet.FilterMode Then Selection.AutoFilter 2, "가락1*"
    
    Set CopyRange = Application.InputBox("복사할 범위를 선택하세요.", Type:=8)
    Set PasteRange = Application.InputBox("붙여넣을 첫번째 셀을 선택하세요.", Type:=8)
    
    Set PasteRange = Range(PasteRange, PasteRange.End(xlDown)).SpecialCells(xlCellTypeVisible)

    i = 1
    For Each c In PasteRange
       CopyRange(i).Copy Destination:=c
       i = i + 1
    Next
End Sub

 
다른 부분은 아래와 같습니다.
    i = 1 : CopyRange 배열의 인덱스 값입니다.
    For Each c In PasteRange : PasteRange의 셀을 하나씩 실행합니다.
       CopyRange(i).Copy Destination:=c : CopyRange의 i 인덱스에 해당하는 셀을 c란 범위에 붙여 넣습니다.
        i = i + 1 : 인덱스 값 i를 1씩 증가시킵니다.
    Next : For문의 끝입니다.
 
CopyRange(i)의 주소를 알기 위해 CopyRange(i)를 마우스로 끌어서 선택한 후 마우스 오른쪽 버튼을 누르고 '조사식 추가' 메뉴를 누릅니다.

매크로 문에서 범위를 선택하여 조사식 추가

 
조사식 추가 창이 표시되면 주소를 알아야 하므로 CopyRange(I) 다음에 .address를 추가하고 확인 버튼을 누릅니다.

조사식 추가 대화상자

 
이번에는 디버그 - 조사식 추가 메뉴를 누른 후 c.address를 입력하고 확인버튼을 누릅니다.

디버그 메뉴에서 조사식 추가

 
그러면 조사식 창이 열리면서 CopyRange(i).address와 c.address가 추가됩니다.

조사식 창에 표시된 식

 
CopyRange(i).Address 등 값을 확인하기 위해 그 왼편 기둥을 클릭해서 중단점을 만듭니다.

변수의 값을 알아보기 위해 중단점 설정

 
이제 실행 아이콘을 눌러 매크로를 실행하고,

실행 아이콘

 
복사할 범위, 붙여 넣을 첫 번째 셀을 지정하면 중단점에서 멈추고, 조사식 창에 CopyRange(i).Address는 $H$25, c.Address는 $H$2로 표시됩니다.

조사식 창에 수식에 대한 값이 표시됨

 
또다시 실행 아이콘을 누르면 H26과 H3셀, H27과 H4셀 등 하나씩 증가하다가 H29와 H6셀이 된 후 한번 더 실행 아이콘을 누르면 H30셀과 H9셀로 되고, 그다음은 H31셀과 H12셀이 됩니다.

조사식 창에 표시된 수식에 대한 값

모두 일곱 번째 셀입니다.

보이는 셀 기준으로 복사할 범위와 붙여 넣을 범위의 순번이 일치함

 
이제 중단점을 클릭해서 지운 다음 실행 아이콘을 누르면 실행이 완료되고, 아래와 같이 숨겨진 셀은 제외하고 보이는 셀에만 붙여 넣기가 됩니다.

복사할 값이 보이는 셀에만 붙여 넣어짐

 
또한 범위가 달라도 하나씩 실행되기 때문에 오류 없이 복사됩니다.
H12셀부터 H21셀까지 지운 다음 실행하는데, 복사할 범위는 H25셀부터 H34셀까지 선택하고 붙여 넣을 첫 번째 셀은 H2셀로 지정하면 복사할 범위는 10개, 붙여 넣을 범위는 6개로 다르지만 오류가 발생하지 않습니다.
 
(약간의 문제점)
H30셀부터 H34셀까지 지운 다음

보이는 셀 12, 15, 18, 21행만 지움 -> 숨겨진 행은 지워지지 않음

 
 
복사할 범위는 H25셀에서 H29셀까지 5개만 선택하고, 붙여넣을 첫번째 셀을 H2셀로 지정하면 복사할 범위는 5개이고, 붙여넣을 범위는 6개이기 때문에 복사할 범위 6번째 값이 공란이기 때문에 H9셀이 지워지는 문제점이 있습니다.

복사할 범위는 5개, 붙여넣을 범위는 6개로 다름

 
(해결책 1)
따라서, CopyRange(i)의 길이가 0보다 클 경우에만 복사해서 붙여 넣도록 하고, 0이라면, 다시 말해 빈칸이라면 끝내는 것(end)으로 코드를 수정해야 합니다.
 

        If Len(CopyRange(i)) > 0 Then
            CopyRange(i).Copy Destination:=c
        Else
            End
        End If

 
CopyRange(i)는 CopyRange.item(i)로 바꿔 쓸 수 있습니다.
 
신기한 것은 CopyRange가 5개로 H29셀까지인데, CopyRange(6).Address가 복사 범위의 아래 H30셀이라는 것입니다.

복사할 범위가 5개인데, CopyRange의 6번째 값이 그 아래셀로 지정됨

그래서 길이가 0이기 때문에 끝이 납니다.
 
(해결책 2)
CopyRange()가 배열이므로 index값의 상한을 구해서 상한까지만 복사하도록 하는 방법이 있습니다.
 

'        인덱스 값 i가 CopyRange배열의 인덱스 값 상한보다 작거나 같다면
        If i <= UBound(CopyRange()) Then
            CopyRange(i).Copy Destination:=c
        Else
            End
        End If

 
Ubound(CopyRange())는 복사할 범위로 5개 셀을 지정했기 때문에 5가 반환됩니다. 따라서, i가 6이 되면 Ubound(CopyRange())보다 크게 되기 때문에 끝나게 됩니다.
 
매번 if문에서 Ubound(CopyRange())를 계산하는 것보다는
아래 매크로문과 같이
limit 변수를 정수 형식으로 선언하고,
limit에 UBound(CopyRange())값을 한 번만 계산해서 대입하도록 하고,
if 문에서 그 값과 i값을 비교하도록 하는 것이 바람직합니다.
 

    Dim limit As Integer
    limit = UBound(CopyRange())
    
    For Each c In PasteRange
'        CopyRange의 i번째 인덱스에 해당하는 값이 있을 때
'        If Len(CopyRange.Item(i)) > 0 Then
'        인덱스 값 i가 CopyRange배열의 인덱스 값 상한보다 작거나 같다면
        If i <= limit Then
            CopyRange(i).Copy Destination:=c
        Else
            End
        End If
        i = i + 1
    Next

서울시 지역 시간별 수질 현황(완성5).xlsm

0.02MB