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

엑셀 VBA #06 / 셀 범위 선택하기_1 [VBA]

1. 기본
Range("A1:D10").Select    또는
Range("A1",Cells(Rows.Count,"D").End(xlup)).Select

2. 응용
D열의 마지막 셀의 값이 없을 때, 위 1의 방법을 적용하면 아래 그림처럼 나온다.

즉 10행의 데이타가 누락된 셀 범위가 설정된다.

이를 해결할 수 있는 방법은 아래 코드를 활용하는 것이다.

2.1) Offset 적용

Range("A1",Cells(Rows.Count,"C").End(xlup).Offset(,1)).Select

2.2)  A열의 행 수를 활용
Dim c As Long
c=Range("A1").End(xlDown).Row
Range("A1:D"&c).Select

2.3) CurrentRegion 적용

Range("A1").CurrentRegion.Select

2.4) UsedRange 적용

worksheets(1).UsedRange.Select

주의할 것은 UsedRange적용시 worksheets가 반드시 들어가야 한다!!!!!!

그리고 장점은 비연속적 데이타 테이블에도 사용할 수 있다는 것이다(아래 그림 참고).

cf, Worksheets(1) vs Sheets(1)의 차이점

Sheets가 더 큰 개념.
Worksheets는 일반적인 데이터가 입력되어 있는 시트를 의미!
Sheets는 일반적 시트에 차트 시트(차트가 시트 전체에 full로 그려진 시트)와 같이 특별한 시트까지 포함

3. 핵심정리

  3.1. 연속범위_1
      Range("A1").CurrentRegion

  3.2. 연속범위_2
      Range("A1",Cells(Rows.Count,"D").End(xlup))
      Range("A1",Cells(Rows.Count,"C").End(xlup).Offset(,1))

https://www.youtube.com/watch?v=qEDUKVAid-Y&t=355s

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

1. 목적

OffSet속성 활용하여 셀 하나 선택하기
- 구조 : 기준셀. Offset(행,열)
- 의미 : 기준셀에서부터 주어진 행렬 만큼 이동하기

2. 사용례
- Range("A1").Offset(1,1).Select

   A1셀을 기준으로 1행 1열 만큼 이동하기

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

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

3. 응용

특정열(ex,C열)의 마지막 데이타값 있는 셀의 아래 셀 선택하기
1단계 : 마지막 테이타값 있는 셀 찾기
=> Cells(Rows.Count,"C").end(xlup)
2단계 : 그 셀의 아래 셀
=> Offset(1,0) 혹은 Offset(1)

- Cells(Rows.Count,"C").end(xlup).Offset(1,0).select 또는
    Cells(Rows.Count,"C").end(3).Offset(1).select 또는

    Cells(Rows.Count,"C").end(3)(2).select

4. 코딩연습

'코딩 방법1

Sub offset연습2()

Dim c As Range

For Each c In Range("C1:C10")
      If c >= 116 Then
            Cells(Rows.Count, "f").End(3)(2) = c
      End If
Next
End Sub

위 코드에서 Range("C1:C10")을 변수로 설정하여 다음과 같이 코딩할 수 있다.

Sub offset연습231229()

Dim rngD As Range
Dim c As Range

Set rngD = Range("c1", Cells(Rows.Count, "c").End(xlUp))

For Each c In rngD
      If c >= 116 Then
            Cells(Rows.Count, "f").End(3)(2) = c
      End If
Next

End Sub

5. 핵심정리

데이터를 누적해서 쌓을 때
Cells(Rows.Count,"a").End(Xlup).Offset(1)
= Cells(Rows.Count,"a").End(3)Offset(2)

엑셀 VBA #07 / 셀 범위 선택하기_2 [VBA] -Resize & Offset

https://www.youtube.com/watch?v=vWWwHWvrzTE&list=PLfxvqpVCYZ8e0qlyc_FU46neoWjO7yTWj&index=5

방법1 : Range("A1:D10").Select

방법2 : Range("A1).CurrentRegion.Select

방법3 : Range("A1", Cells(Rows.Count, "D").End(xlUp)).Select

만약 D10셀이 빈셀이라면... 위 방법1,2,3은 제대로 작동될까.....

방법1,2는 이상무, 그러나 방법3는 엉뚱한 결과가 나온다. 아래 그림처럼.

그래서 해결방법은.........?????

아래의 4번 혹은 5번을 적용하면 된다.

방법4 : Range("A1", Cells(Rows.Count, "C").End(xlUp).Offset(, 1)).Select

방법5 : Dim c As Long
           c = Range("A1").End(xlDown).Rows
           Range("A1:D" & c).Select

[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

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

용도

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