VBA 유튜브 학습기 5 - 데이터의 마지막 행과 열 자동으로 찾기 (feat. 파워 문과 기획자)
데이터가 입력된 마지막 행과 열을 자동으로 찾아서,
매번 코드를 수정하지 않아도 자동으로 조건에 맞는 값에 표시를 해 주는 방법이라니.
주제부터 흥미로워.🤩
엑사남 님 강의 5강 듣고 왔습니다.
이동 중 차 안에서 인트로 영상을 찍으셨던데...
바쁜 시간 쪼개서 강의 올려 주신 거 같아서 너무 감사하더라고요.
그럼 내가 나중에 까먹을 게 뻔해서 하는 5강 내용 정리 시작해 볼게요.
[예제1]
이런 데이터가 있을 때 지금까지는 아래와 같이 코딩을 했습니다.
Sub test01()
Dim i As Long
For i = 2 To 14
If Cells(i, 2) <= 70 Then
Cells(i, 2).Font.ColorIndex = 3
End If
Next
End Sub
* Font.ColorIndex = 3 : 빨간색을 의미한다고 합니다.
이렇게 변수 i가 순환하는 범위를 고정된 값이 2부터 14로 정해 놓았었죠.
코드를 작성한 후에 실행해 보면
2행부터 14행까지 순환하면서 조건에 맞는 값을 표시해 줍니다.
그런데 변수를 저렇게 2에서 14로 고정해 놓으면 누구나 생각할 수 있을 만한 문제가 발생합니다.
데이터는 늘 추가되잖아요.....
이렇게 데이터의 양이 늘어난 경우에, 처음 코딩한 대로 두고 실행을 하면
네... 이렇게 14행 이후의 행들은 조건에 맞는 값도 표시를 하지 못하고 누락하게 됩니다.
엑셀은 죄가 없어요. 코드가 2행부터 14행까지만 순환하도록 작성됐으니까요.
그럼 이런 생각을 하게 되죠.
"데이터가 추가로 입력되면 그 범위까지 자동으로 바뀌었으면 좋겠다."
그런 방법은 없을 줄 알았는데 있더라고요?
우선 선생님을 따라서 코드를 작성해 볼게요.
Sub test01()
Dim i As Long
Dim lngR As Long
lngR = Range("B10000").End(xlUp).Row
For i = 2 To lngR
If Cells(i, 2) <= 70 Then
Cells(i, 2).Font.ColorIndex = 3
End If
Next
End Sub
못 보던 것이 등장했습니다.
lngR이라는 변수를 새로 설정했어요.
그리고 lngR에 이런 값을 집어넣습니다.
lngR = Range("B10000").End(xlUp).Row
매 강의마다 멘붕이 한 번씩 오는데 오늘은 여깁니다. ㅋㅋㅋ
머슨 말이고?
위 코드의 내용은 이렇습니다. 이렇다고 합니다.
B10000셀(B열 저~~~~~ 밑에 어딘가에 있는 셀이겠죠?)을 범위의 마지막으로 보고(End), 거기서부터 찾아 올라와라(xlUp) 어디까지? 데이터가 입력돼 있는 행까지(Row)
코드를 위와 같이 작성한 후에 실행해 보면?
이렇게 2행부터 14행을 벗어난 행(15행부터~)도 데이터를 찾아서 표시해 줍니다.
데이터가 이렇게까지 늘어나도 될까요?
네. 됩니다. 😎
뭔가 멋있어.
[예제2]
엑사남 님 강의가 너무 좋은 게 응용력 0인 문과의 마음을 너무 잘 아시는 거 같단 거예요.
이번엔 행, 열 구조를 바꿔서도 해 봅니다.
데이터가 이렇게 열을 기준으로 늘어나는 거죠.
누군가가 데이터를 추가한다면 K열에 추가하겠죠?
그럼 예제1에서 배운 아래에서부터 찾아 올라와서 마지막 행을 찾는 건 아무 의미가 없잖아요.
Sub test01()
Dim i As Long
Dim lngC As Long
lngC = Range("IV2").End(xlToLeft).Column
For i = 2 To lngC
If Cells(2, i) <= 70 Then
Cells(2, i).Font.ColorIndex = 3
End If
Next
End Sub
데이터가 입력된 마지막 열을 찾으려면?
저~~~쪽 오른쪽 끝에서부터 데이터가 입력된 마지막 열을 찾아 왼쪽으로 와야겠죠?
코드에도 이런 내용이 들어가 있습니다.
lngC = Range("IV2").End(xlToLeft).Column
* 엑셀의 마지막 열이 IV 열이래요. 저도 처음 알았습니다.
코드의 내용은 이렇습니다.
IV2셀(저~~~~~ 오른쪽 어딘가에 있는 셀이겠죠?)을 범위의 마지막으로 보고(End), 거기서부터 왼쪽으로 찾아와라(ToLeft) 어디까지? 데이터가 입력돼 있는 열까지(Column)
코드를 실행해 보면?
이렇게 데이터가 입력된 마지막 셀인 J열까지 찾아와서 조건에 맞는 값들을 표시해 줍니다.
데이터가 O열까지 추가되도 될까요?
네. 됩니다.
쩔어.......👏
[과제]
매번 있는 과제의 시간이 돌아왔습니다.
행과 열의 마지막 데이터를 찾아서 조건에 맞는 값에 표시하게 하시오.(1,000점)
아, 뭔가 어려워.....
선생님의 힌트.
구구단 코드를 짰던 것처럼 IF문을 중복으로 쓰면 된다.
아 오케이 오케이👌👌👌
사실 거기까지 듣고 "아! 이렇게 하면 되네!" 이렇게 명확하게 떠오른 건 아니지만
일단 작성하기 시작하면 대충 뭔가 알 거 같다 그런 느낌이었어요.
이 정도만 해도 일취월장한 거죠. 내 자신 너무 대견한 것. 감격😛
Sub homework()
Dim i As Long
Dim j As Long
Dim lngR As Long
Dim lngC As Long
lngR = Range("B10000").End(xlUp).Row
lngC = Range("IV2").End(xlToLeft).Column
For i = 2 To lngR
For j = 2 To lngC
If Cells(i, j) <= 70 Then
Cells(i, j).Font.ColorIndex = 3
End If
Next
Next
End Sub
코드를 이렇게 작성했습니다.
제가 작성했지만 복잡해 보이니까 더 뿌듯하네요. ㅋㅋㅋ
굉장히 어려운 일을 해낸 것 같잖아.
Dim i As Long
Dim j As Long
행과 열이 모두 고정 값이 아니기 때문에 변수로 지정하기 위해 각각 i와 j로 변수 설정을 했습니다.
둘 다 정수인 Long 형으로 지정했고요.
여기까지 쓰고 깊은 번뇌.......🙄😫😱
Dim lngR As Long
Dim lngC As Long
행도 열도 각각 가장 밑에서부터 위로, 가장 오른쪽에서부터 왼쪽으로 데이터가 입력된 마지막 셀을 찾아와야 하니 또 각각 변수 설정을 해 줍니다.
그리고 다시 찾아온 고뇌의 시간 ㅋㅋㅋㅋ
lngR = Range("B10000").End(xlUp).Row
lngC = Range("IV2").End(xlToLeft).Column
B10000셀을 마지막 행으로 보고 밑에서부터 위로 데이터가 입력된 행을 찾아서 lngR에 집에넣고,
IV2셀을 마지막 열로 보고 오른쪽부터 왼쪽으로 데이터가 입력된 열을 찾아서 lngC에 집어 넣도록 설정해 주고요.
For i = 2 To lngR
For j = 2 To lngC
i는 2부터 lngR 행까지, j는 2부터 lngC 열까지 순환하게 합니다.
If Cells(i,j) <= 70 Then
Cells(i,j).Font.ColorIndex = 3
i와 j를 각각 행과 열에 집어넣어 셀을 지정하고, 그 셀의 값이 70보다 작거나 같으면,
셀의 폰트 컬러 인덱스 값을 3(=빨간색)으로 지정합니다.
이렇게 코드 작성을 마치고 실행해 보면
이렇게!
범위 내 셀들 중 조건에 맞는 셀들의 값이 빨간색으로 바뀌었습니다.
이렇게 데이터가 추가된 시트에서 실행해 봐도
이렇게! 잘 작동하네요.
5강까지 들으니까 왠지 실무에 쓸 만한 지식이 좀 쌓이는 거 같아요.
막상 실무에 적용할 때는 이번에 과제할 때 느꼈던 고민과 번뇌 x 9,999,999,999 정도의 고통을 느끼겠지만....
한 발씩 나아가고 있단 이 느낌이 굉장히 뿌듯합니다.
엑사남 님, 좋은 강의 감사합니다!
저는 이제 일하러.....😂