EXCEL - VBA

윗셀과 같은 값 지우기(1) - for 문

별동산 2020. 7. 9. 05:46
반응형

데이터베이스를 만들게 되면 필드를 기준으로 데이터를 입력하므로, 같은 데이터가 두번이상 표시되게 됩니다.

아래 데이터를 보면 대구광역시와 부천시만 한번씩이고, 나머지는 2번이상 표시되고 있습니다.

​

이와 같은 경우에 중복되는 값을 지우게 되면 보기 더 편하게 됩니다.

윗셀과같은셀지우기.xlsx
0.01MB

​

1. 중복값 지우기 매크로 1

​

가. 모듈 추가

​

시명칭이 윗 셀과 동일하다면 맨 윗 것만 남겨두고, 나머지는 빈 셀로 표시하는 것입니다. 이런 것은 VBA로 처리할 수밖에 없습니다.

​

개발도구 - Visual Basic으로 들어간 다음

Visual Basic 실행

​

윗셀과같은셀지우기.xlsx 파일이 선택된 상태에서, 삽입 - 모듈을 클릭하거나

모듈 삽입

​

사용자정의폼 등 콤보상자 버튼을 누른 후 모듈을 클릭해서

​

윗셀과같은셀지우기.xlsx 파일에 모듈을 추가합니다. 그러면 아래와 같이 xlsx 파일 아래 모듈과 Module1이 생기고, 오른쪽에 빈 코드창이 열립니다. 만약 오른쪽에 빈 코드창이 보이지 않는다면 모듈을 클릭하고, Module1을 더블 클릭하면 됩니다.

코드 창

​

​

나. 코드(매크로) 작성

​

세번째 행부터 마지막줄까지 윗줄과 값이 같은지 비교해서 같다면 그 셀을 지우는 것을 반복하면 됩니다.

​

⑴ 프로시져명 기재

​

매크로의 단위를 프로시져라고 하는데,

sub 윗셀과같은값지우기 라고 입력하고 엔터키를 치면

아래와 같이 괄호와 end sub가 자동으로 생깁니다.

프로시져명에 공백이 있으면 안됩니다.

프로시져 삽입

​

​

⑵ 반복문

반복문에 for문, while문 등이 있지만 한 줄씩 증가하면서 실행하고, 마지막줄에 끝나야 하기 때문에 for문이 편리한데, while문으로도 가능하므로 두 가지 다 해보겠습니다.

​

㈎ for문 1

for문은 시작값과 종료값, 간격이 필요한데, 시작값은 3으로 정해져 있고, 물론 경우에 따라 다를 수 있습니다, 종료값은 A열의 마지막행에서 위로 올라와서 멈추는 행이 됩니다. 간격은 당연히 1이며, 1은 생략가능합니다.

​

그렇다면 마지막행은

range("a1048576").end(xlup).row 로 구합니다.

A열의 마지막 행은 1048576(MS 365 버전 기준으로서, 버전에 따라 다르므로 ctrl + ↓ 키를 눌러 확인)이므로 a1048576이라고 셀 표시를 했는데, 대소문자를 구분하지 않으므로 A1048576이라고 하지 않아도 됩니다.

​

그리고, 이 셀에서 맨 위로 올라오려면 Ctrl + ↑ 키를 누르면 되는데, 이것을 매크로로는 end(xlUp)이라고 표현합니다. 위로 끝까지 올라오라는 의미인데, 올라오다가 셀값이 있으면 그 셀에 멈추게 됩니다. 따라서, 그 줄이 데이터가 있는 맨 아래줄이 되는 것입니다. xlUp도 xlup으로 입력하면 자동으로 xlUp으로 변환되므로 소문자로 입력해도 됩니다.

​

.row는 행수를 반환해 줍니다. 만약 열을 알고자 하면 .column 이라고 하면 됩니다.

​

그리고, 행수를 저장할 변수를 지정한 다음 값을 저장하면 되는데,

​

변수선언은 dim EndRow as integer라고 하고,

값 저장은 EndRow = range("a1048576").end(xlup).row 라고 하면 됩니다.

​

integer 입력시 in까지만 입력해도 Integer가 표시되므로 Tab키를 눌러 쉽게 입력할 수 있고,

혹시 in 누르고 위 화면이 나왔는데, 다른데를 눌러서 이 화면이 없어졌다면 in에 커서를 놓고(앞, 중간, 뒤 관계없음) Ctrl + j키를 누르면 위와 같이 선택 창이 표시됩니다.

​

또한 EndRow = range("a1048576").end(xlup).row 입력시에도, .e만 쳐도 e로 시작하는 단어들이 표시되는데, end가 두번째 줄에 있으므로 아래 화살표키로 이동 후 탭키를 누르거나,

​

en까지 누르면 end가 선택되므로 이 때 Tab키를 눌러도 됩니다.

​

또한 end( 까지 입력하면 그 안에 들어갈 요소들이 아래와 같이 나열되므로, 화살표키로 이동하던가 xlu까지 입력한 후 Tab키를 눌러도 됩니다.

​

다음 row도 r까지 입력하면 r로 시작하는 것들이 보이고,

​

ro까지 입력하면 row가 표시되므로 탭키를 치면 됩니다. 참 편리하죠?

​

for 문 안에도 값이 하나씩 증가해야 하니 변수가 필요합니다. 통상적으로 i를 많이 사용하니

dim i as integer 라고 선언하면 됩니다. 그러나, 이미 dim 문이 있으므로 합해서

Dim EndRow As Integer, i as Integer 라고 하면 됩니다.

 

둘 다 as integer라고 해서 생략하면 integer가 아닌 variant(가변형, 엑셀에서 정수, 문자 등 타입을 정함) 타입이 되므로 생략하면 안되고, 변수마다 써줘야 합니다.

​

이제 for 문을 작성할 준비가 다 됐습니다. 그런데, 간격 1은 생략 가능하므로

for i = 3 to EndRow 가 됩니다. 그리고, next 로 for문을 닫습니다.

Option Explicit

Sub 윗셀과같은값지우기()
    Dim EndRow As Integer, i as Integer
    
    EndRow = Range("a1048576").End(xlUp).Row
    
    For i = 3 To EndRow
    
    Next
    
End Sub

 

⑶ 조건문

이제 윗셀값과 같은지 비교하는 조건문, if문을 작성해야 합니다.

​

A열이고, i를 이용해야 하므로

처리하고 있는 셀은 Range("a" & i)가 되고,

윗줄은 Range("a" & i-1) 이 됩니다.

 

정확하게 입력한다면 (i-1)이 돼야 하지만 수식 계산 우선 순위에 따라 i-1을 한 후 &(결합연산자)에 의해 "a"와 결합되므로 괄호를 입력하지 않아도 되는 것입니다.

 

(연산자 우선 순위표)

연산자 설명
:(콜론)
(하나의 공백)
,(쉼표)		 참조 연산자
– 음수(예: –1)
% 백분율
^ 거듭제곱
* 및 / 곱하기와 나누기
+ 및 – 더하기와 빼기
& 두 개의 텍스트 문자열 연결
=
< >
<=
>=
<>		 비교

​

셀 표시는 Range 다음에 열과 행을 지정하면 되는데, A3셀이라면 Range("a" & 3) 이 됩니다. 다시 말해, 열은 큰따옴표안에 열에 해당하는 문자를 입력하고, 행은 숫자인데 i이므로 & 연산자로 결합해서 표현하고 괄호를 닫아 줍니다.

​

윗셀은 열은 같고 행만 다르고, 행이 i-1이 되므로, Range("a" & i-1) 라고 표현한 것입니다. 

​

if문은 마지막에 then을 추가해야 하며, end if 로 닫습니다. 따라서 현재까지 작성한 코드는 아래와 같습니다.

Option Explicit

Sub 윗셀과같은값지우기()
    Dim EndRow As Integer, i As Integer
    
    EndRow = Range("a1048576").End(xlUp).Row
    
    For i = 3 To EndRow
        If Range("a" & i) = Range("a" & i - 1) Then
        
        End If
    Next
    
End Sub

 

이제 셀을 지우는 문장을 작성해야 하는데, 셀은 range("a" & i) 이고,

내용을 지우는 명령은 홈탭의 편집 그룹에 있는데, 지우기 명령은 '모두 지우기', '서식 지우기' 등이 있습니다.

​

이것을 VBA의 명령어와 연결시키면 아래와 같습니다.

모두 지우기 .Clear
서식 지우기 .ClearFormats
내용 지우기 .ClearContents
설명 및 메모 지우기 .ClearComments
하이퍼링크 해제 .ClearHyperlinks
하이퍼링크 제거 .Hyperlinks.Delete

​

따라서, 작업중인 셀의 내용을 지우는 것은 range("a" & i).ClearContents 가 됩니다.

Option Explicit

Sub 윗셀과같은값지우기()
    Dim EndRow As Integer, i As Integer
    
    EndRow = Range("a1048576").End(xlUp).Row
    
    For i = 3 To EndRow
        If Range("a" & i) = Range("a" & i - 1) Then
            Range("a" & i).ClearContents
        End If
    Next
    
End Sub

 

이제 실행 - Sub/사용자 정의 폼 실행 메뉴를 클릭하거나, 왼쪽 세모 아이콘을 눌러 실행하면 되는데,

​

매크로 실행은 Ctrl + Z키를 눌러도 돌이킬 수 없으므로 저장 후 실행하는 것이 좋습니다.

​

파일 - 저장 메뉴를 누르거나, Ctrl + S 키를 누르면, '매크로 제외 통합 문서에 저장할 수 없다'고 하면서 예, 아니오를 묻는데, 아니오를 선택한 후

​

확장자로 xlsm을 선택하고, 저장 버튼을 누릅니다.

​

이제 세모 아이콘을 눌러 매크로를 실행합니다.

실행후 엑셀로 돌아오면 서울특별시가 4개가 연속되므로 1개만 남고 3개가 지워져야 하는데, 건너뛰어서 지워졌습니다.

​

이유를 생각해보니 내용이 지워져서 비교할 값이 없어지기 때문입니다. 그렇다면 내용이 지워졌을 때는 바로 윗셀 이 아니라 올라가서 만나는 셀과 비교하면 됩니다.

​

⑷ 조건문 수정

​

셀 내용이 지워졌다는 것은

range("a" & i-1)의 길이가 0인 경우이므로, 이를 if 문으로 표현하면

if len(range("a" & i-1)) = 0 then 이 되고,

​

윗셀값이 아니라, 올라가서 만나는 셀은 EndRow를 구할 때처럼

range("a" & i).end(xlUP) 으로 구하면 됩니다.

​

따라서 아래 조건식,

len(range("a" & i-1)) = 0 and range("a" & i) = range("a" & i).end(xlUP)

을 기존 조건문과 then 사이에 or로 연결해서 추가하면 됩니다.

​

기존 내용이 일부 지워진 상태로 저장하면 매크로가 제대로 실행되는지 체크하기 어려우므로, 엑셀을 닫는데 저장하냐고 물으면 '저장 안함'을 선택하고, 다시 윗셀과같은셀지우기.xlsm을 엽니다.

​

xlsm 파일을 열면 보안 경고가 표시되면서 오른쪽에 '콘텐츠 사용'버튼이 있는데 누릅니다.

매크로 보안 경고 - 콘텐츠 사용

​

개발도구 - Visual Basic 명령을 누른 후 if문을 아래와 같이 수정합니다.

If Range("a" & i) = Range("a" & i - 1) _

Or (Len(Range("a" & i - 1)) = 0 And Range("a" & i) = Range("a" & i).End(xlUp)) Then

​

if문 첫줄 끝에 있는 _ 표시는 두 줄이 연결된다는 표시이며,

두 개의 조건 중 하나만 만족하면 되므로 or로 연결하는데, 중간에 and 문이 있어 괄호로 감싼 것입니다.

​

전체 매크로문은 아래와 같습니다. 다시 저장한 후 실행하면

Option Explicit

Sub 윗셀과같은값지우기()
    Dim EndRow As Integer, i As Integer
    
    EndRow = Range("a1048576").End(xlUp).Row
    
    For i = 3 To EndRow
        If Range("a" & i) = Range("a" & i - 1) _
            Or (Len(Range("a" & i - 1)) = 0 And Range("a" & i) = Range("a" & i).End(xlUp)) Then
            Range("a" & i).ClearContents
        End If
    Next
    
End Sub

​

이제 서울특별시가 하나만 표시됩니다.

​

⑸ for문 2

​

줄을 삭제하거나, 내용을 지울 때는 위와 같이 위에서부터 아래로 진행할 것이 아니라 아래에서부터 위로 올라오면서 처리하는 것으로 바꾸면 값이 지워져서 비교할 수 없는 문제를 간단하게 해결할 수 있습니다. 왜냐하면 아래부터 지워지고 비교할 대상은 위에 그대로 남아 있기 때문입니다.

​

수정한 코드는 아래와 같으며, 엑셀 파일을 저장하지 않고 닫은 다음, 열고 아래 코드를 추가합니다.

모듈명 뒤에 2를 추가해서 다른 프로시져를 만들었습니다. 실행하면 if문의 조건이 하나인데도 결과는 같습니다.

Sub 윗셀과같은값지우기2()
    Dim EndRow As Integer, i As Integer
    
    EndRow = Range("a1048576").End(xlUp).Row
    
    For i = EndRow To 3 Step -1
        If Range("a" & i) = Range("a" & i - 1) Then
            Range("a" & i).ClearContents
        End If
    Next
    
End Sub

​

아래 파일을 참고하기 바라며,

윗셀과같은셀지우기.xlsm
0.02MB

 

While문과 시와 구 2개를 결합해서 비교한 후 지우는 것은 다음 편에서 다루도록 하겠습니다.

반응형
저작자표시 비영리

'EXCEL - VBA' 카테고리의 다른 글

엑셀의 유용함, 사용자 정의함수(2)  (0) 2020.07.28
엑셀의 유용함, 사용자 정의함수(1)  (0) 2020.07.27
엑셀 날짜 관련 함수 총정리(5) (VBA 날짜관련 함수 - DateAdd, DateDiff, DatePart 등)  (0) 2020.07.20
개인용 매크로 통합문서, personal.xlsb  (0) 2020.07.10
윗셀과 같은 값 지우기(2) - while 문  (0) 2020.07.10

'EXCEL - VBA'의 다른글

  • 현재글윗셀과 같은 값 지우기(1) - for 문

관련글

티스토리툴바