엑셀 VBA #88 / 시트통합_VBA필수 [VBA]

Sub MergeData_By_For_Next()
      Dim i As Long
      
      Application.ScreenUpdating = False
      
      Sheet1.Cells.Clear
      
      For i = 2 To 4
      'sheet2번부터 4번까지(1월~3월시트)//// 4를 'Worksheets.Count'로 하는 게 High Class
            Sheets(i).Range("a1").CurrentRegion.Copy _
            Sheet1.Cells(Rows.Count, "a").End(xlUp).Offset(1)
            '~~~~~~End(xlUp).Offset(1) => ~~~~~End(3)(2)
      Next
      
      Application.ScreenUpdating = True
End Sub

Sub MergeData_By_For_Each_Next()

      Dim sh As Worksheet
      Dim i As Long
      
      Application.ScreenUpdating = False
      
      Sheet1.Cells.Clear
      
      i = 2
      For Each sh In ThisWorkbook.Worksheets
            'if sh.name<>activesheet.name then
            If sh.Name <> Sheet1.Name Then   
            'If sh<>Sheet1 Then하면 에러인데...왜?
                  Sheets(i).Range("a1").CurrentRegion.Copy _
                  Sheet1.Cells(Rows.Count, "a").End(xlUp).Offset(1)
                  i = i + 1
            End If
      Next

      Application.ScreenUpdating = True
      
End Sub

Sub MergeData_By_Do_Loop()

      Dim cnt As Long, i As Long
      
      Application.ScreenUpdating = False
      
      Sheet1.Cells.Clear
      
      cnt = Worksheets.Count
      
      i = 2
      
      Do While i <= cnt
        Sheets(i).Range("a1").CurrentRegion.Copy _
        Sheet1.Cells(Rows.Count, "a").End(xlUp).Offset(1)
        i = i + 1
      Loop
      Application.ScreenUpdating = True
End Sub

Sub MergeData_By_For_Next실무형()
      Dim i As Long
      Dim rng As Range
      
      Application.ScreenUpdating = False
      
      Sheet1.Cells.Clear
      
      Sheet1.Range("a1:f1") = Array("월", "필드1", "필드2", "필드3", "필드4", "필드5")
      For i = 2 To Worksheets.Count
            Sheets(i).Range("a1").CurrentRegion.Copy _
            Sheet1.Cells(Rows.Count, "B").End(xlUp).Offset(1)
            
            Set rng = Sheet1.Range("a1").CurrentRegion
            rng.SpecialCells(xlCellTypeBlanks) = Sheets(i).Name
      Next
      Application.ScreenUpdating = True
End Sub

https://www.youtube.com/watch?v=lo7x3cG7rLI&list=PLfxvqpVCYZ8e0qlyc_FU46neoWjO7yTWj&index=89

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