엑셀 VBA #41 / 행 삭제_개선2 [VBA] - 배열

Sub 행삭제_배열_myexr231224()

Dim r As Variant   '모든 데이타를 넣을 배열방r....variant적용
Dim a()            '배열방r의 데이타 중 'A-1'이외 값을 넣을 배열방a....동적배열적용
Dim i As Long, j As Long, k As Long

'-----------------------------------------------------
'값을 각 배열방에 넣는 과정
r = Range("A1", Cells(Rows.Count, "B").End(xlUp))
ReDim a(1 To UBound(r, 1), 1 To 2)   
'동적배열의 영역 확정, a는 2차원이므로 a(1차원,2차원)으로 표시해야함
'1차원 크기는 1부터 r의 행 갯수, 2차원 크기는 1부터 2열까지

For i = 1 To UBound(r, 1) ' UBound(r,1)=UBound(1): r배열의  1차원 위치의 최대값까지
      If Cells(i, "A") <> "A-1" Then   'cells(i,"A")의 값이 "A-1"이 아니면
            j = j + 1    'j의 초기값이 지정이 없으므로 지정해야하는데, 
            'j값은 1부터 시작해야 하므로 'j=j+1'코드 설정함
            For k = 1 To 2    '열변호를 확장시킬 변수가 필요함
                  a(j, k) = r(i, k) 'i는 r방에 대응하는 변수이므로,
                  'i를 a방에 대응할 수 없으니 별도의 변수j를 대입함                  
            Next
      End If
Next
'------------------------------------------

'-------------------------------------------
'a방 배열 값을 뿌려주기
Range("A1").CurrentRegion.ClearContents
Range("A1").Resize(UBound(a, 1), 2) = a  
'셀A1을 기준으로 "a배열의 1차원 최대값 및 2차원 최대값"만큼 리사이즈한 영역에 a배열값을 넣어준다.
'-------------------------------------------
End Sub

https://www.youtube.com/watch?v=c7rkH3le9cs&list=PLfxvqpVCYZ8e0qlyc_FU46neoWjO7yTWj&index=41

엑셀 VBA #36 / 행삽입_기본 [VBA]

1. 목적 : VBA에서 행삽입 기능 숙지
2. Insert
- 구문 : 개체.Insert Shift:=xldown
- 의미 : 정해진 영역에서 기존 행은 아래로 이동시키고 새로운 행 삽입

3. 사용

Dim i As Long, lR As Long

lR = Cells(Rows.Count, "A").End(xlUp).Row   ' "A"열의 데이타가 있는 마지막 셀의 행번호

For i = 2 To lR
    If i <> lR Then  '마지막 행 아래는 빈 행이 있으므로, 마지막 행은 행삽입 불요 
        Cells(i, "A").EntireRow.Insert shift:=xlDown
    End If
Next

오류발생원인 : 하나하나의 행 삽입되면서 기존의 행번호도 같이 변경되기때문!

해결위한 1차 코드
Dim i As Long, lR As Long

lR = Cells(Rows.Count, "A").End(xlUp).Row

For i = lR To 2 Step -1  '마지막 번호부터 시작하면서 행번호를 줄여간다
    If i <> lR Then
        Cells(i, "A").EntireRow.Insert shift:=xlDown
    End If
Next

오류발생......

해결위한 2차 코드
Dim i As Long, lR As Long

lR = Cells(Rows.Count, "A").End(xlUp).Row

For i = lR To 2 Step -1 
    If i <> lR Then
        Cells(i, "A").Offset(1).Insert shift:=xlDown
        '작업해야 할 행보다 1칸 아래 행을 선정하여 행 삽입하면 됨
    End If
Next

4. 심화

Dim i As Long, lR As Long

lR = Cells(Rows.Count, "A").End(xlUp).Row

For i = lR To 2 Step -1
    If i <> lR Then
        Cells(i, "A").Offset(1).Resize(, 5).Insert shift:=xlDown
        'Resize(,5) : 5열까지만 행삽입토록
    End If
Next

https://www.youtube.com/watch?v=Lz1no-0Z7Xk&list=PLfxvqpVCYZ8e0qlyc_FU46neoWjO7yTWj&index=36

엑셀 VBA #31 / 정렬_2 [VBA] - 예제

1. 예제1 : 생년 기준으로 오름차순 정렬

  Dim rng As Range

  Set rng = Range("A1").CurrentRegion
  Set rng = rng.Offset(, 1).Resize(, rng.Columns.Count - 1)
    ' rng변수이름 동일 적용하여, 순차적 적용 !!!!!!자세히 볼것, 
    'Offset(, 1) =offset(0,1)
    '***Resize(, rng.Columns.Count - 1) = =Resize(rng.Rows.Count, rng.Columns.Count - 1) 

        =Resize(3, rng.Columns.Count - 1)

  rng.Sort rng(3, 1), xlAscending, Orientation:=xlSortRows

2. 예제2

For Each c In Range("A1:A7")  'c는 A1 or A2 or......A7
    Set rng = Range(c.Offset(0, 1), c.End(xlToRight)) 'a1에서부터 a1으로부터 셀값이 있는 오른쪽 셀까지
    If rng.Count > 1 Then      'rng 영역 셀 갯수가 1보다 크면 아래 구문 적용
        rng.Sort rng(1, 1), xlDescending, Orientation:=xlSortRows
    End If        'rng 영역 셀 갯수가 1보다 작으면 위 구문 미적용
Next

Offset

- 구조 : 기준셀. Offset(행,열)
- 의미 : 기준셀에서부터 주어진 행렬 만큼 이동하기

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

     !!!!! rng.Resize(,3).Select  = rng.Resize(rng.Rows.Count,3).Select 

           Resize(,3)의 의미는 Resize(0,3)가 아니라~~~

https://www.youtube.com/watch?v=y52qcbqjpJI&list=PLfxvqpVCYZ8e0qlyc_FU46neoWjO7yTWj&index=31

엑셀 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 #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

[Sample Code]

Sub Resize_test()

    Dim rngT As Range
    Dim cn As Long
    
    Set rngT = Range("A1").CurrentRegion
   
    cn = Cells(1, Columns.Count).End(xlToLeft).Column
    rngT.Offset(1).Resize(rngT.Rows.Count - 1, cn).Select

End Sub

해석1.

   cn = Cells(1, Columns.Count).End(xlToLeft).Column

1행의 마지막 데이타 셀까지의 열 개수 계산

해석2.

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

머릿글 행을 제외한 데이타 데이블 전체 선택

용도

행 또는 열방향으로 데이타가 추가되더라도 테이블 전체를 유동적으로 선택할 수 있음

형식(문법) : 기준개체.Resize(행,열)

ex)

   기준개체.Resize(2,3)

   기준개체.Resize(,3).Select
= 기준개체.Resize(기준개체의 행 갯수 전체,3).Select

[Sample Code]

Sub Resize_test()

    Dim rngT As Range
    Set rngT = Range("A1").CurrentRegion

    rngT.Resize(2, 3).Select

End Sub

rngT.Resize(2, 3).Select

rngT.Resize(, 3).Select

rngT.Resize(rngT.Rows.Count, 3).Select

rngT.Resize(, 3).Select  = rngT.Resize(rngT.Rows.Count, 3).Select