엑셀 VBA #15 / 셀 범위 선택하기_10 [VBA]

Sub 빈셀_채우기()

Dim rng As Range

Set rng = Range("A1", Cells(Rows.Count, "A").End(xlUp).Offset(1))

rng.SpecialCells(xlCellTypeBlanks).Formula = "=A2"

End Sub

SpecialCell 

구조 : 기준개체.Specialcells(옵션)

Offset

Range("B5").Offset(2,0).Select 또는
Range("B5").Offset(2).Select    ',0은 생략가능함

https://www.youtube.com/watch?v=qbeX24HXVxg&list=PLfxvqpVCYZ8e0qlyc_FU46neoWjO7yTWj&index=15

엑셀 VBA #14 / 셀 범위 선택하기_9 [VBA] - Intersect 실무예제


1. 목적 : 실무사례에서 Intersect가 어떻게 사용되는지 확인
2. 의도 : 특정 영역의 셀에 문자 입력 불가토록 함(즉 셀 값이 숫자 또는 빈 셀이어햐 함)
3. 적용기법 : 위크시트 이벤트(모듈이 아님), Intersect, IsNumeric

4. 코드

A2~D11 영역의 셀에는 문자 입력 불가토록 코딩함

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo er     '만약 에러가 발생하면, 아래의 er로 가라
If Not Intersect(Target, Columns("A:D")) Is Nothing Then
    If Target.Row > 1 And Target.Row < 12 Then
        If VBA.IsNumeric(Target) Then
        Else
            MsgBox   "숫자로 입력하세요"
            Target = ""        '타겟 셀에 문자등을 입력하였다면 그 문자를 삭제하는 코드
            Target.Select    '문자 삭제한 후, 커서가 다음 열 혹은 행으로 이동하지 않고 다시 타겟셀에 있도록하는 코드
            End If
        End If
End If

er:

End Sub

vba - intersect, event프로시져

https://www.youtube.com/watch?v=NBO4iXAEAhw&list=PLfxvqpVCYZ8e0qlyc_FU46neoWjO7yTWj&index=14

엑셀 VBA #13 / 셀 범위 선택하기_8 [VBA] - Intersect

목적 : Intersect를 활용하여 범위간의 교집합 셀 선택하기

1. Intersect
구조 : Application.Intersect(범위1,범위2,,,,,,범위30)
          Application생략 가능
의미 : 교집합 셀 선택(최대 30개)

2. 사용
Intersect(Range("A1:D10"),Range("C4:E12")).Select

3. 심화
만약 범위간 교집합이 없을 때, 위와 같이 단독구문으로 하면 프로시져가 멈추게 된다. 이를 해결하기 위해 아래처럼 구문을 작성하면 된다.
If Not Intersect(Range("A1:D10"),Range("C4:E12")) Is Nothing Then
~~~~~~~~ '교집합이 있으면 이곳에 실행할 코드 입력
End IF

4. 참고
합집합을 구하는 함수 : Union
Union(Range("A1:D10"),Range("C4:E12")).Select


https://www.youtube.com/watch?v=C0CtxRTnqz4&list=PLfxvqpVCYZ8e0qlyc_FU46neoWjO7yTWj&index=13

엑셀 VBA #12 / 셀 범위 선택하기_7 [VBA]

1. 목적 : SpecialCell & Areas활용하여 빈 행 삭제

2. 예시구문
  2.1. 한 행만 남기고 나머지 빈 행 전체를 삭제

  Dim rng As Range
  Dim a As Range

  Set rng = Range("A1", Cells(Rows.Count, "A").End(xlUp))

  For Each a In rng.SpecialCells(xlCellTypeBlanks).Areas
      If a.Count > 1 Then
          a.Resize(a.Count - 1, 1).EntireRow.Delete
      End If
  Next
  End Sub

rng.SpecialCells(xlCellTypeBlanks).Areas

=> rng영역내에서 빈 셀의 영역 전체

a.Resize(a.Count - 1, 1).EntireRow.Delete

  2.2. 한 행은 남기고 나머지 특정 열까지만 삭제

  Dim rng As Range
  Dim a As Range

  Set rng = Range("A1", Cells(Rows.Count, "A").End(xlUp))

  For Each a In rng.SpecialCells(xlCellTypeBlanks).Areas
      If a.Count > 1 Then
          a.Resize(a.Count - 1, 4).Delete
      End If

https://www.youtube.com/watch?v=wkyqM-ki-_0&list=PLfxvqpVCYZ8e0qlyc_FU46neoWjO7yTWj&index=12

엑셀 VBA #11 / 셀 범위 선택하기_6 [VBA]

1. 목적 : (비연속적)다중영역의 셀 주소 확인 가능
구조 : 기준개체.Areas
의미 : 다중영역의 전체를 한 묶음으로 지정???

(VBA#12 04:36)
    For each   in rng.SpecialCells(xlCellTypeBlanks).Areas

    rng영역에서 값이 없는 빈셀의 다중영역 전체!!!!!!!

2. 사용
  2.1. 선택된 셀이 하나인 경우
     MsgBox Selection.Address
     선택 셀의 주소가 절대주소로 보여짐

     MsgBox Selection.Address(0,0)
     선택 셀의 주소가 상대주소로 보여짐

  2.2. 선택된 셀이 다중인 경우(ex, 4개 영역)
     MsgBox Selection.Areas(1).Address(0,0)
     MsgBox Selection.Areas(2).Address(0,0)
     MsgBox Selection.Areas(3).Address(0,0)
     MsgBox Selection.Areas(4).Address(0,0)

3. 심화활용
선택된 셀 영역의 갯수를 모르는 경우 : For Each ~ Next문 & 동적배열 사용
Dim a As Range
Dim adr()
Dim i As Long

For Each a In Selection.Areas
    ReDim Preserve adr(i)          'adr은 영역의 각각의 주소를 담는 변수
    adr(i) = a.Address(0, 0)
    i = i + 1
Next

MsgBox "선택된 다중범위는 " & vbCr & Join(adr, vbCr)

https://www.youtube.com/watch?v=lVh9T7V21ZE&list=PLfxvqpVCYZ8e0qlyc_FU46neoWjO7yTWj&index=11

엑셀 VBA #09 / 셀 범위 선택하기_4 [VBA]

목적 : SpecialCells를 활용하여 빈 셀, 오류 셀, 수식 셀을 선택하기

1. SpecialCells
구조 : 기준개체.Specialcells(옵션)
의미 : 기준개체에서 옵션에 맞는 셀을 선택

2. 사용
  2.1. 빈 셀 선택(옵션코드 xlCelllTypeBlanks)
  Dim rng As Range
  Set rng = Range("A1").CurrentRegion

  rng. SpecialCells(xlCelllTypeBlanks).Select

  cf) 빈 셀에 '**'넣기
  rng. SpecialCells(xlCelllTypeBlanks) = "**"

  2.2. 오류 셀 선택(옵션코드 xlCellTypeFormulas,xlErrors)
  Dim rng As Range
  Set rng = Range("A1").CurrentRegion

  rng. SpecialCells(xlCellTypeFormulas,xlErrors).Select

  cf) 오류 셀에 지우기
  rng. SpecialCells(xlCellTypeFormulas,xlErrors) =""

  2.3. 수식 셀 선택(옵션코드 xlCellTypeFormulas)
  Dim rng As Range
  Set rng = Range("A1").CurrentRegion

  rng. SpecialCells(xlCellTypeFormulas).Select

    2.3.1. 만약 rng 영역에 수식 셀이 없다면!!!!!
    2.3 의 코드만 입력시 오류 발생하기 때문에 아래처럼 코드를 작성해야한다.
    Dim rng As Range
    Set rng = Range("A1").CurrentRegion

   On Error Resume Next
   rng. SpecialCells(xlCellTypeFormulas).Select
   If Err Then MsgBox "이 워크시트에는 수식이 없습니다."
   On Error GoTo 0    '발생된 에러를 메모리상에서 지워라!!는 의미


https://www.youtube.com/watch?v=PtiKv9lR3Q8&list=PLfxvqpVCYZ8e0qlyc_FU46neoWjO7yTWj&index=9

엑셀 VBA #32 / 자동필터_1 [VBA] ----SpecialCells(xlCellTypeVisible)

엑셀 VBA #08 / 셀 범위 선택하기_3 [VBA]
 
목적 : Union을 활용하여 비연속적으로 떨어진 셀 범위 선택하기(합집합)

1. Union
구조 : Application.Union(범위1,범위2,,,,,,범위30)
          Application생략 가능
의미 : 비연속적으로 떨어진 셀 범위(최대 30개)를 합체

2. 사용
Application.Union(Columns("B"),Columns("D")).Select      또는
Union(Columns("B"),Columns("D")).Select


3. 심화

특정값(ex, 4 또는 6)을 제외한 셀을 선택하는 경우, Union활용

Dim c as Range
Dim r as Range

For Each c in Range("A1:A10")
    If c = 4 or c = 6 then
    Else                          'c의 값이 4 또는 6이 아니라면
           If r Is Nothing Then   ' r이라는 개체변수(r방)에 아무것도 없으면
               Set r =c                      'r방에 c값을 넣어주고
           Else                           'r방에 기존 값이 있으면
               Set r =Union(r,c)        ' r방에 기존 값과 c값을 합체한다
           End if
    End If
Next

If r is nothing = 0 then r.Select       '0=false, r방이 nothing이 아니라면(false), r을 선택해라

https://www.youtube.com/watch?v=w3fWBhtPamA&t=15s

2023.12.13 - 엑셀 VBA #13 / 셀 범위 선택하기_8 [VBA] - Intersect

2023.12.14 - 엑셀 VBA #40 / 행 삭제_개선1 [VBA] - Union

2023.12.15 - 엑셀 VBA #50 / 중복데이터 처리_3 [VBA]

2023.12.16 - 엑셀 VBA #51 / 중복데이터 처리_4 [VBA] - 사용자정의함수

엑셀 VBA #07 / 셀 범위 선택하기_2 [VBA]

목적 : Resize & Offset을 활용하여 플렉시블하게 셀 범위 선택하기

1. Resize
구조 : 기준개체.Resize(행,열)
의미 : 기준개체를 바탕으로 행과 열만큼 영역을 재지정

2. 사용
Dim rng As Range
Set rng =range("A1").CurrentRegion

rng.Resize(2,3).Select

Resize(,3)의 의미는 Resize(0,3)가 아니라~~~
!!!!! rng.Resize(,3)  = rng.Resize(rng.Rows.Count,3)

rng.Rows.Count : rng영역의 행의 갯수를 산출

그러면 rng.Resize(3)은 어떤 의미일까........?

rng.Resize(3, 열전체)를 의미한다. 즉 rng.Resize(3,rng.columms.count)이다.

3. 셀 범위가 가변적인 경우, Resize & Offset 활용

제목행을 제외한 데이타 테이블 전체 셀 범위를 선택하기!!!

Dim rng As Range
Dim cn As Long
Set rng =range("A1").CurrentRegion

cn=Cells(1,Columns.Count).End(xlToLeft).Column
'열번호 찾기 : 1행의 데이타 유무와 관계없이 1행의 마지막셀에 가서 열번호를 보여줘.

rng.Offset(1).Resize(rng.Rows.Count-1,cn).Select

'Offset(1) : rng영역에서 행 방향으로 1행 이동 = Offset(1,0)

'rng.Rows.Count-1 : rng영역의 전체 행의 갯수에서 제목행 개수(1)을 빼기

4. 핵심정리

특정 범위 잡을 때, 제목행 빼고, 마지막 열도 계산

cn = Cells(1, Columns.Count).End(xlToLeft).Column
rng.Offset(1, 0).Resize(rng.Rows.Count - 1, cn)

특정열의 마지막 셀 (중간 빈 셀O) 번호 찾기
Cells(4, Columns.Count).End(xlToLeft).Column

특정행의 마지막 셀(중간 빈 셀O) 번호 찾기
Cells(Rows.Count, "A").End(xlUp).Row

엑셀 VBA #03 / 셀 하나 선택하기_2[VBA] - End속성 이용

엑셀 VBA #05 / 셀 하나 선택하기_4 [VBA]-Offset

https://www.youtube.com/watch?v=VN9mj9vrnsw&t=5s