작업으로 세포와 범위에서 Excel VBA(선택,복제,이동,수정하)

1월 2, 2021 admin 0 Comments

작업을 할 때는 엑셀,대부분의 시간을 보냈다고 워크시트에서 영역을 다루는 세포와 범위를 다룹니다.VBA 를 사용하여 Excel 에서 작업을 자동화하려는 경우 vba 를 사용하여 셀 및 범위로 작업하는 방법을 알아야합니다.

VBA 의 범위(예:선택,복사,이동,편집 등)로 할 수있는 많은 다른 것들이 있습니다.).

그래서이 주제를 다루기 위해이 튜토리얼을 섹션으로 나누고 예제를 사용하여 Excel VBA 에서 셀 및 범위로 작업하는 방법을 보여줍니다.,

시작합시다.

이 튜토리얼에서 언급 한 모든 코드는 VB 편집기에 배치해야합니다. 어떻게 작동하는지 알기 위해’VBA 코드를 넣을 위치’섹션으로 이동하십시오.

당신이 vba 쉬운 방법을 학습에 관심이 있다면,내 온라인 엑셀 VBA 교육을 확인하십시오.

이 있습니다:

를 선택하면 세포/범위에서 엑셀을 사용하여 VBA

을 작품으로 세포 및 범위 엑셀에서 사용 VBA,당신이 선택할 필요가 없습니다.대부분의 경우 셀 또는 범위를 선택하지 않는 것이 좋습니다(우리가 볼 수 있듯이).,

그럼에도 불구하고이 섹션을 살펴보고 어떻게 작동하는지 이해하는 것이 중요합니다. 이것은 당신의 VBA 학습에 결정적 일 것이며 여기에서 다루는 많은 개념들이이 튜토리얼 전체에서 사용될 것입니다.

그래서 아주 간단한 예제부터 시작하겠습니다.

선택하면 단일 세포를 사용하여 VBA

을 선택하려면 단일 세포에서의 활성 시트(말 A1),다음 사용할 수 있습니다 아래 코드:

Sub SelectCell()Range("A1").SelectEnd Sub

위의 코드는 필수’이하’및’끝 Sub’부분과 코드의 라인을 선택하는 셀 A1.,

Range(“A1”)는 참조하려는 셀의 주소를 VBA 에 알려줍니다.

Select 는 Range 객체의 메소드이며 Range 객체에 지정된 셀/범위를 선택합니다. 셀 참조를 큰 따옴표로 묶어야합니다.

이 코드는 차트 시트가 활성 시트 인 경우 오류를 표시합니다. 차트 시트에는 차트가 포함되어 있으며 널리 사용되지 않습니다. 그 안에 셀/범위가 없기 때문에 위의 코드는 그것을 선택할 수 없으며 결국 오류가 표시됩니다.활성 시트에서 셀을 선택하려면 셀 주소를 지정하기 만하면됩니다.,그러나 다른 시트(Sheet2 라고 가정 해 봅시다)에서 셀을 선택하려면 먼저 Sheet2 를 활성화 한 다음 그 안에있는 셀을 선택해야합니다.

Sub SelectCell()Worksheets("Sheet2").ActivateRange("A1").SelectEnd Sub

마찬가지로,당신은 또한 활성화를 통합,다음 활성화 특정 워크시트에서 그것은,그리고 다음을 선택합니다.

Sub SelectCell()Workbooks("Book2.xlsx").Worksheets("Sheet2").ActivateRange("A1").SelectEnd Sub

통합 문서를 참조 할 때 파일 확장자(.위의 코드에서 xlsx). 통합 문서가 저장되지 않은 경우 파일 확장자를 사용하지 않아도됩니다.,

이러한 사례는 매우 유용하지만,당신이 볼 것이 나중에는 방법이 튜토리얼에서 우리가 사용할 수 있는 동일한 개념을 복사 및 붙여넣기 세포 엑셀에서 사용하여(VBA).셀을 선택하는 것처럼 범위를 선택할 수도 있습니다.

범위의 경우 고정 크기 범위 또는 가변 크기 범위 일 수 있습니다.

고정 크기 범위에서는 범위가 얼마나 큰지 알 수 있으며 VBA 코드에서 정확한 크기를 사용할 수 있습니다. 그러나 가변 크기의 범위를 사용하면 범위가 얼마나 큰지 전혀 알지 못하며 VBA 마법을 조금 사용해야합니다.이 작업을 수행하는 방법을 살펴 보겠습니다.,

수정 크기 범위 선택

다음은 범위 A1:D20 을 선택하는 코드입니다.

Sub SelectRange()Range("A1:D20").SelectEnd Sub

하는 다른 방법이 사용하고 아래 코드:

Sub SelectRange()Range("A1", "D20").SelectEnd Sub

위의 코드는 왼쪽 위 셀 주소(A1)고 오른쪽 하단 셀 주소(D20)및 선택은 전체 범위에 있습니다. 이 기술은 가변적 인 크기의 범위로 작업 할 때 유용 해집니다(이 자습서의 뒷부분에서 끝 속성이 다루어 질 때 알 수 있듯이).,

선택이 다른 통합 문서 또는 다른 워크 시트에서 발생하려면 VBA 에 이러한 개체의 정확한 이름을 알려야합니다.

예를 들어,아래 코드는 book2 통합 문서의 Sheet2 워크 시트의 범위 A1:D20 을 선택합니다.

Sub SelectRange()Workbooks("Book2.xlsx").Worksheets("Sheet1").ActivateRange("A1:D20").SelectEnd Sub

자,얼마나 많은 행이 있는지 모르는 경우 어떻게해야합니까? 그 안에 값이 있는 모든 셀을 선택하려면 어떻게 해야 할까요.

이러한 경우 다음 섹션에 표시된 방법을 사용해야합니다(가변적 인 크기 범위 선택시).,

가변 크기 범위 선택

셀 범위를 선택할 수있는 다른 방법이 있습니다. 선택하는 방법은 데이터 구성 방법에 따라 다릅니다.

이 섹션에서는 vba 에서 범위로 작업 할 때 실제로 유용한 몇 가지 유용한 기술을 다룰 것입니다.

선택 사용 CurrentRange 속성

경우에는지 알고 얼마나 많은 행/열 데이터를 사용할 수 있습니다 CurrentRange 속성 범위의 객체입니다.

CurrentRange 속성은 데이터 범위의 모든 연속 채워진 셀을 포함합니다.,

아래는 셀 A1 을 보유하고있는 현재 영역을 선택하는 코드입니다.

Sub SelectCurrentRegion()Range("A1").CurrentRegion.SelectEnd Sub

위의 방법이 좋을 때 당신은 모든 데이터를 가지고 있으로 테이블 없이 빈 행이/에서 열니다.

지만 경우에 당신은 빈 행이/에서 열 데이터를,그것은이없는 사람을 선택한 후에 빈 행이/열이 있습니다. 아래 이미지에서 CurrentRegion 코드는 행 11 이 비어 있으므로 행 10 까지 데이터를 선택합니다.

이러한 경우에,당신은 당신을 사용하고자 할 수 있습 UsedRange 워크시트의 속성을 개체입니다.,

UsedRange 속성을 사용하여 선택

UsedRange 를 사용하면 변경된 모든 셀을 참조 할 수 있습니다.

따라서 아래 코드는 활성 시트의 모든 사용 된 셀을 선택합니다.

Sub SelectUsedRegion()ActiveSheet.UsedRange.SelectEnd Sub

주는 경우에 당신은 멀리 휴가 사용되었다,그것은 것으로 간주하여 위의 코드와 모든 셀 때까지 사용되는 것 셀룰라는 것을 선택합니다.

끝 속성을 사용하여 선택

자,이 부분은 정말 유용합니다.

끝 속성을 사용하면 마지막으로 채워진 셀을 선택할 수 있습니다., 이를 통해 컨트롤 다운/업 화살표 키 또는 컨트롤 오른쪽/왼쪽 키의 효과를 모방 할 수 있습니다.예제를 사용하여 이것을 이해하고 이해합시다.

가 있다고 가정해 봅시 데이터 집합을 아래와 같이고 신속하게 선택하는 마지막으로 가득 세포에서 열 A.

여기서 문제는 데이터를 변경할 수 있으며,당신은 알지 못하는 방법은 많은 세포가 가득합니다. 키보드를 사용하여이 작업을 수행해야하는 경우 셀 A1 을 선택한 다음 Control+Down arrow 키를 사용할 수 있으며 열에서 마지막으로 채워진 셀을 선택합니다.

이제 VBA 를 사용하여이 작업을 수행하는 방법을 살펴 보겠습니다., 이 기술은 편리하려는 경우 신속하게 이동을 마지막으로 가득 세포에서는 가변 크기의 열

Sub GoToLastFilledCell()Range("A1").End(xlDown).SelectEnd Sub

위의 코드는 이동을 마지막으로 가득 세포에서 열 A.

마찬가지로,사용할 수 있습니다 최종(xlToRight)이동을 마지막으로 가득 셀합니다.

Sub GoToLastFilledCell()Range("A1").End(xlToRight).SelectEnd Sub

자,무엇을 선택하려면 전체 열에 대해 마지막으로 가득 셀입니다.,

당신이 할 수 있는 코드를 사용하여 아래

Sub SelectFilledCells()Range("A1", Range("A1").End(xlDown)).SelectEnd Sub

위 코드에서,우리가 사용하는 첫 번째와 마지막의 세포이 필요하다는 선택합니다. 얼마나 많은 채워진 셀이 있더라도 위의 코드는 모두 선택합니다.

기억 위 예제는 우리가 선택한 범위 A1:D20 를 사용하여 다음과 같은 라인의 코드:

범위(“A1″,”D20”)

여기에 A1 었는 왼쪽 위 셀 D20 었 오른쪽 하단에 있는 셀 범위에 있습니다. 우리는 가변적 인 크기의 범위를 선택하는 데 동일한 논리를 사용할 수 있습니다., 그러나 오른쪽 아래 셀의 정확한 주소를 모르기 때문에 끝 속성을 사용하여 가져 왔습니다.범위(“A1”,범위(“A1”)에서

.첫 번째 셀과 범위(“A1”)를 나타냅니다.끝(xlDown)은 마지막 셀을 나타냅니다. 두 참조를 모두 제공 했으므로 Select 메서드는이 두 참조 사이의 모든 셀을 선택합니다.

마찬가지로 행과 열이 여러 개인 전체 데이터 세트를 선택할 수도 있습니다.

아래 코드는 셀 A1 에서 시작하여 채워진 모든 행/열을 선택합니다.,

Sub SelectFilledCells()Range("A1", Range("A1").End(xlDown).End(xlToRight)).SelectEnd Sub

위의 코드에서 Range(“A1”)를 사용했습니다.끝(xlDown).끝(xlToRight)데이터 세트의 오른쪽 아래 채워진 셀의 참조를 가져옵니다.

사이의 차이를 이용 CurrentRegion 과 끝

대신에 사용할 최종 제공하는 선택을 가득 범위가 있는 경우 CurrentRegion 제공하겠습니다

끝 속성으로 시작 셀을 지정할 수 있습니다., 예를 들어,데이터가 있는 경우에 A1:D20 지만,첫 번째 행에는 헤더를 사용할 수 있습 End 속성을 선택하는 데이터 없이 헤더에(아래 코드를 사용하여).

Sub SelectFilledCells()Range("A2", Range("A2").End(xlDown).End(xlToRight)).SelectEnd Sub

그러나 CurrentRegion 은 헤더를 포함하여 전체 데이터 세트를 자동으로 선택합니다.

지금까지이 튜토리얼에서는 다른 방법을 사용하여 셀 범위를 참조하는 방법을 보았습니다.

이제 실제로 이러한 기술을 사용하여 일부 작업을 수행 할 수있는 몇 가지 방법을 살펴 보겠습니다.,

셀을 복사/범위를 사용하여 VBA

제가 언급했듯이 시작 부분에서 선택,셀룰라이 필요하지 않 작업을 수행할 수 있습니다. 당신은 심지어이를 선택하지 않고 셀과 범위를 복사하는 방법이 섹션에서 볼 수 있습니다.간단한 예제부터 시작하겠습니다.

단일 셀 복사

셀 A1 을 복사하여 셀 D1 에 붙여 넣으려는 경우 아래 코드가 수행합니다.

Sub CopyCell()Range("A1").Copy Range("D1")End Sub

range 객체의 복사 방법은 셀을 복사하여(Control+C 와 마찬가지로)지정된 대상에 붙여 넣습니다.,위의 예제 코드에서 대상은 복사 방법을 사용하는 동일한 줄에 지정됩니다. 를 만들고 싶다면 당신의 코드를 더 읽기 쉬운 사용할 수 있습니다,아래 코드:

Sub CopyCell()Range("A1").Copy Destination:=Range("D1")End Sub

위의 코드는 복사 및 붙여넣기 값뿐만 아니라 서식/식니다.

이미 눈치 챘을 수도 있듯이 위의 코드는 셀을 선택하지 않고 복사합니다. 워크 시트의 위치에 상관없이 코드는 셀 A1 을 복사하여 D1 에 붙여 넣습니다.또한 위의 코드는 d2 셀의 기존 코드를 덮어 씁니다., Excel 을 덮어 쓰지 않고 셀 D1 에 이미 무언가가 있는지 알려주고 싶다면 아래 코드를 사용할 수 있습니다.

사를 수정 크기의 범위

복사하려는 경우 A1:D20 에 J1:M20 사용할 수 있습니다,아래 코드:

Sub CopyRange()Range("A1:D20").Copy Range("J1")End Sub

대상 셀,당신은 주소를 지정해야의 왼쪽 위 셀입니다. 코드는 정확한 복사 된 범위를 대상에 자동으로 복사합니다.동일한 구조를 사용하여 한 시트에서 다른 시트로 데이터를 복사 할 수 있습니다.

아래 코드는 a1:d20 을 활성 시트에서 Sheet2 로 복사합니다.,

Sub CopyRange()Range("A1:D20").Copy Worksheets("Sheet2").Range("A1")End Sub

위의 내용은 활성 시트의 데이터를 복사합니다. 따라서 코드를 실행하기 전에 데이터가있는 시트가 활성 시트인지 확인하십시오. 안전을 위해 데이터를 복사하는 동안 워크 시트의 이름을 지정할 수도 있습니다.

Sub CopyRange()Worksheets("Sheet1").Range("A1:D20").Copy Worksheets("Sheet2").Range("A1")End Sub

좋은 것에 대해 위의 코드는 상관 없이는 장이 활성화,그것은 항상에서 데이터를 복사 Sheet1 붙여 Sheet2.

참조 대신 이름을 사용하여 명명 된 범위를 복사 할 수도 있습니다.,

예를 들어’SalesData’라는 명명 된 범위가있는 경우 아래 코드를 사용하여이 데이터를 Sheet2 에 복사 할 수 있습니다.

Sub CopyRange()Range("SalesData").Copy Worksheets("Sheet2").Range("A1")End Sub

경우 범위의 지명 범위 전체에 통합 문서,당신이 할 필요가 없 장에 있는 지명 범위를 실행합니다. 명명 된 범위는 통합 문서의 범위가 지정되어 있으므로이 코드를 사용하여 모든 시트에서 액세스 할 수 있습니다.

Table1 이라는 이름의 테이블이 있으면 아래 코드를 사용하여 Sheet2 에 복사 할 수 있습니다.

Sub CopyTable()Range("Table1").Copy Worksheets("Sheet2").Range("A1")End Sub

범위를 다른 통합 문서에 복사 할 수도 있습니다.,

다음 예제에서는 Excel 테이블(Table1)을 Book2 통합 문서에 복사합니다.

Sub CopyCurrentRegion()Range("Table1").Copy Workbooks("Book2.xlsx").Worksheets("Sheet1").Range("A1")End Sub

이 코드는 통합 문서가 이미 열려있는 경우에만 작동합니다.

복사하는 변수 크기의 범위

방법 중 하나 복사하는 변수 크기의 범위를 변환하는 것으로 이러한 지명 범위 또는 Excel 테이블과 코드를 조합하여 다음과 같은 이전 섹션입니다.

하지만 그렇게 할 수 없다면 range 객체의 CurrentRegion 또는 End 속성을 사용할 수 있습니다.

아래 코드는 활성 시트의 현재 영역을 복사하여 Sheet2 에 붙여 넣습니다.,

Sub CopyCurrentRegion()Range("A1").CurrentRegion.Copy Worksheets("Sheet2").Range("A1")End Sub

경우 복사하려는 첫 번째 열의 데이터 설정 마지막까지 가득 셀 붙여 Sheet2 사용할 수 있습니다,아래 코드:

Sub CopyCurrentRegion()Range("A1", Range("A1").End(xlDown)).Copy Worksheets("Sheet2").Range("A1")End Sub

복사하려는 경우 행뿐만 아니라 열을 사용할 수 있습니다,아래 코드:

Sub CopyCurrentRegion()Range("A1", Range("A1").End(xlDown).End(xlToRight)).Copy Worksheets("Sheet2").Range("A1")End Sub

이러한 모든 코드를 선택하지 않는 세포는 동안 실행됩니다. 일반적으로 작업하기 전에 실제로 셀/범위를 선택해야하는 경우는 소수에 불과합니다.,

객체 변수에 범위 할당

지금까지 우리는 셀의 전체 주소(예:통합 문서(“Book2.xlsx”).워크 시트(“Sheet1”).범위(“A1”)).코드를보다 관리하기 쉽게하려면 이러한 범위를 객체 변수에 할당 한 다음 해당 변수를 사용할 수 있습니다.

예를 들어,아래 코드,내가 할당 소스 및 대상 범위를 개체 변수를 사용한 변수를 복사본에서 데이터를 하나의 범위는 다른.

우리는 범위 객체로 변수를 선언하여 시작합니다., 그런 다음 Set 문을 사용하여 이러한 변수에 범위를 할당합니다. 범위가 변수에 할당되면 간단히 변수를 사용할 수 있습니다.

다음 빈 셀에 데이터 입력(입력 상자 사용)

입력 상자를 사용하여 사용자가 데이터를 입력 할 수 있습니다.

예를 들어,당신은 데이터로 설정을 아래와 당신을 입력하려는 판매고를 사용할 수 있는 입력란에 VBA. 코드를 사용하여 다음 빈 행의 데이터를 채우는지 확인할 수 있습니다.,위의 코드는 VBA 입력 상자를 사용하여 사용자로부터 입력을 가져온 다음 지정된 셀에 입력을 입력합니다.

정확한 셀 참조를 사용하지 않았다는 점에 유의하십시오. 대신 끝 및 오프셋 속성을 사용하여 마지막 빈 셀을 찾고 그 안에 데이터를 채 웁니다.

이 코드는 사용할 수 없습니다. 예를 들어,만약 당신이 입력한 텍스트 문자열을 입력 상자 요구에 대한 양 또는 금액을 알 수 있습니다 Excel 할 수 있습니다. If 조건을 사용하여 값이 숫자인지 아닌지를 확인한 다음 그에 따라 허용 할 수 있습니다.,

반복을 통해 세포/범위

지금까지 우리가 볼 수 있는 방법을 선택하고,복사하고,데이터를 입력하에서 세포와 범위를 다룹니다.

이 섹션에서는 범위의 셀/행/열 집합을 반복하는 방법을 살펴 보겠습니다. 이는 각 셀을 분석하고이를 기반으로 일부 작업을 수행하려는 경우에 유용 할 수 있습니다.

예를 들어,당신은 당신이 보고 싶은 모든 세 번째 행에서 선택 그런 다음 필요하신 루프를 통해 확인에 대한 행 번호입니다., 마찬가지로 글꼴 색상을 빨간색으로 변경하여 모든 음수 셀을 강조 표시하려는 경우 각 셀의 값을 반복하고 분석해야합니다.다음은 선택한 셀의 행을 반복하고 대체 행을 강조 표시하는 코드입니다.

Sub HighlightAlternateRows()Dim Myrange As RangeDim Myrow As RangeSet Myrange = SelectionFor Each Myrow In Myrange.RowsIf Myrow.Row Mod 2 = 0 ThenMyrow.Interior.Color = vbCyanEnd IfNext MyrowEnd Sub

위의 코드를 사용하는 모드 기능을 확인하는 행 번호를 선택합니다. 행 번호가 짝수이면 시안 색으로 강조 표시됩니다.

다음은 코드가 각 셀을 통과하고 그 안에 음의 값을 갖는 셀을 강조 표시하는 또 다른 예입니다.,

Sub HighlightAlternateRows()Dim Myrange As RangeDim Mycell As RangeSet Myrange = SelectionFor Each Mycell In MyrangeIf Mycell < 0 ThenMycell.Interior.Color = vbRedEnd IfNext MycellEnd Sub

참고 당신이 할 수 있는 동일한 것을 사용하여 조건부 서식(는 역동적이고 더 좋은 방법이 이렇게하려면). 이 예제는 루핑이 vba 에서 셀 및 범위와 함께 작동하는 방법을 보여주는 용도로만 사용됩니다.

VBA 코드를 어디에 넣을 지

Vba 코드가 Excel 통합 문서에서 어디로 가는지 궁금하십니까?

Excel 에는 VBA 편집기라는 VBA 백엔드가 있습니다. Vb 편집기 모듈 코드 창에 코드를 복사하여 붙여 넣어야합니다.

이 작업을 수행하는 단계는 다음과 같습니다.

개발자 탭으로 이동하십시오.,
Visual Basic 옵션을 클릭하십시오. 그러면 백엔드에서 VB 편집기가 열립니다.
프로젝트 탐색기에서 창에서 VB 편집,마우스 오른쪽 버튼으로 클릭하여 개체에 대한 통합 문서를 삽입하려는 코드입니다. 프로젝트 탐색기가 보이지 않으면보기 탭으로 이동하여 프로젝트 탐색기를 클릭하십시오.
삽입으로 이동하여 모듈을 클릭하십시오. 그러면 통합 문서의 모듈 개체가 삽입됩니다.
모듈 창에 코드를 복사하여 붙여 넣습니다.,

다음 Excel 자습서를 좋아할 수도 있습니다.

Vba 를 사용하여 워크 시트 작업.
VBA 를 사용하여 통합 문서 작업.
Excel 에서 사용자 정의 함수 만들기.
Excel VBA 의 다음 루프 용-예제가 포함 된 초보자 가이드.
Excel VBA InStr 함수를 사용하는 방법(실용적인 예제 포함).
엑셀 VBA Msgbox.
Excel 에서 매크로를 기록하는 방법.
Excel 에서 매크로를 실행하는 방법.
Excel 에서 추가 기능을 만드는 방법.,
Excel 개인 매크로 통합 문서/저장&모든 통합 문서에서 매크로를 사용하십시오.
엑셀 VBA 이벤트-쉬운(완전한)가이드.
Excel VBA 오류 처리.
Vba(단계별 가이드)를 사용하여 Excel 에서 데이터를 정렬하는 방법.
VBA 초보자를위한 24 가지 유용한 Excel 매크로 예제(즉시 사용 가능).리>