마지막으로 사용한 셀 값을 찾으려면 다음을 사용하십시오.
Dim LastRow As Long
LastRow = Range("E4:E48").End(xlDown).Row
Debug.Print LastRow
단일 요소를 셀에 넣을 때 잘못된 출력이 발생합니다. 그러나 셀에 둘 이상의 값을 넣으면 출력이 정확합니다. 이것의 이유는 무엇입니까?
답변
참고 : Correct
마지막 행을 찾는 방법을 사용할 수있는 “원 스톱 포스트”로 만들고 싶습니다 . 마지막 행을 찾을 때 따라야 할 모범 사례도 다룹니다. 따라서 새로운 시나리오 / 정보를 접할 때마다 계속 업데이트하겠습니다.
마지막 행을 찾는 신뢰할 수없는 방법
매우 신뢰할 수없는 마지막 행을 찾는 가장 일반적인 방법 중 일부는 사용해서는 안됩니다.
- 중고 범위
- xlDown
- CountA
UsedRange
해야 결코 데이터가있는 마지막 셀을 발견하는 데 사용할 수 없습니다. 매우 신뢰할 수 없습니다. 이 실험을 시도하십시오.
cell에 무언가를 입력하십시오 A5
. 이제 아래 주어진 방법 중 하나를 사용하여 마지막 행을 계산하면 5가 표시됩니다. 이제 셀의 A10
색을 빨간색으로 지정하십시오. 이제 아래 코드 중 하나를 사용하면 여전히 5를 얻습니다. Usedrange.Rows.Count
무엇 을 사용 하면 얻을 수 있습니까? 5가되지 않습니다.
UsedRange
작동 방식 을 보여주는 시나리오는 다음과 같습니다 .
xlDown
똑같이 신뢰할 수 없습니다.
이 코드를 고려
lastrow = Range("A1").End(xlDown).Row
A1
데이터 가있는 하나의 셀 ( ) 만있는 경우 어떻게 됩니까? 워크 시트의 마지막 행에 도달하게됩니다! 셀 A1
을 선택한 다음 End키를 누른 다음 키 를 누르는 것과 같습니다 Down Arrow. 또한 범위 내에 빈 셀이있는 경우 신뢰할 수없는 결과를 제공합니다.
CountA
사이에 빈 셀이 있으면 잘못된 결과를 얻을 수 있기 때문에 신뢰할 수 없습니다.
따라서 하나의 사용을 피해야한다 UsedRange
, xlDown
그리고 CountA
마지막 셀을 찾을 수 있습니다.
열에서 마지막 행 찾기
Col E에서 마지막 행을 찾으려면 이것을 사용하십시오.
With Sheets("Sheet1")
LastRow = .Range("E" & .Rows.Count).End(xlUp).Row
End With
당신이 우리에게 .
이전 이 있음을 알게되면 Rows.Count
. 우리는 종종 그것을 무시하기로 결정했습니다. 발생할 수있는 오류에 대해서는 이 질문을 참조하십시오 . 나는 항상 및 .
이전 Rows.Count
을 사용하는 것이 Columns.Count
좋습니다. 이 질문은 Excel 2003 및 이전 버전과 Excel 2007 및 이후 버전에 대한 Rows.Count
반환 65536
으로 인해 코드가 실패하는 전형적인 시나리오 1048576
입니다. 마찬가지로 Columns.Count
반환 256
및 16384
각각.
엑셀 2007 이상이 가지고 위의 사실 1048576
도 행은 우리가 항상 같은 행 값을 보유 할 변수 선언해야한다는 사실을 강조하는 Long
대신 Integer
다른 당신이 얻을 것이다 Overflow
오류입니다.
이 방법은 숨겨진 행을 건너 뜁니다. 위의 열 A에 대한 위의 스크린 샷을 살펴보면 행 8이 숨겨져 있으면이 접근법은 5
대신에 반환 됩니다 8
.
시트에서 마지막 행 찾기
Effective
시트에서 마지막 행 을 찾으려면 이것을 사용하십시오. 의 사용에 주목하십시오 Application.WorksheetFunction.CountA(.Cells)
. 워크 시트에 데이터가있는 셀이 없으면 다음 .Find
을 제공 하기 때문에 필요 합니다.Run Time Error 91: Object Variable or With block variable not set
With Sheets("Sheet1")
If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
lastrow = .Cells.Find(What:="*", _
After:=.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
Else
lastrow = 1
End If
End With
테이블에서 마지막 행 찾기 (ListObject)
예를 들어, 표의 세 번째 열에서 마지막 행을 가져 오는 것과 동일한 원칙이 적용됩니다.
Sub FindLastRowInExcelTableColAandB()
Dim lastRow As Long
Dim ws As Worksheet, tbl as ListObject
Set ws = Sheets("Sheet1") 'Modify as needed
'Assuming the name of the table is "Table1", modify as needed
Set tbl = ws.ListObjects("Table1")
With tbl.ListColumns(3).Range
lastrow = .Find(What:="*", _
After:=.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
End With
End Sub
답변
참고 :이 답변은 이 의견에 의해 동기 부여되었습니다 . 목적은 UsedRange
위의 답변에서 언급 한 것과 다릅니다.
마지막으로 사용한 셀을 찾는 올바른 방법에 대해서는 먼저 사용 된 것으로 판단한 다음 적절한 방법을 선택해야합니다 . 나는 적어도 세 가지 의미를 생각합니다.
-
사용 = 공백이 아닌, 즉 갖는 데이터를 .
-
Used = “… 사용 중 . 데이터 또는 서식 이 포함 된 섹션을 의미합니다 .”
공식 문서에 따르면, 이것은 저장시 Excel에서 사용되는 기준입니다. 이 공식 문서를 참조하십시오 . 이를 알지 못하는 경우이 기준은 예상치 못한 결과를 낳을 수 있지만, 예를 들어 데이터가 없을 수있는 특정 영역을 강조 표시하거나 인쇄하기 위해 의도적으로 악용 될 수도 있습니다. 물론 통합 문서를 저장할 때 작업의 일부를 잃지 않도록 사용할 범위의 기준으로 사용하는 것이 바람직합니다. -
Used = “… 사용 중, 데이터 또는 서식 이 포함 된 섹션 또는 조건부 서식 ” 을 의미합니다 .
2와 동일하지만 조건부 서식 규칙의 대상인 셀도 포함합니다.
마지막으로 사용한 셀에 따라 달라집니다 찾는 방법 당신이 (당신의 기준을)합니다 .
기준 1의 경우이 답변을 읽는 것이 좋습니다 . 참고 UsedRange
신뢰할 수없는 인용된다. 데이터를 포함하는 마지막 셀을 단순히보고하는 것이 UsedRange
아니라 오해의 소지가 있다고 생각합니다 (예 : “불공정” ) UsedRange
. 따라서이 답변에 표시된 대로이 경우에는 사용해서는 안됩니다. 이 주석 도 참조하십시오 .
기준 2의UsedRange
경우이 용도로 설계된 다른 옵션과 비교할 때 가장 신뢰할 수있는 옵션 입니다. 마지막 셀이 업데이트되도록 통합 문서를 저장할 필요조차 없습니다.
Ctrl+ End는 저장하기 전에 잘못된 셀로 이동합니다 ( http://msdn.microsoft.com/en-us/library/aa139976%28v=office.10% 에서“워크 시트를 저장할 때까지 마지막 셀은 재설정되지 않습니다”)
29.aspx . 이것은 오래된 참조이지만이 점에서 유효합니다).
기준 3의 경우 내장 방법을 모릅니다 . 기준 2는 조건부 서식을 설명하지 않습니다. UsedRange
또는 Ctrl+로 감지되지 않는 수식을 기반으로 서식이 지정된 셀이있을 수 있습니다 End. 그림에서 마지막 셀은 서식이 명시 적으로 적용되었으므로 B3입니다. 셀 B6 : D7에는 조건부 서식 규칙에서 파생 된 형식이 있으며이 형식은에 의해 감지되지 않습니다 UsedRange
. 이를 위해서는 VBA 프로그래밍이 필요합니다.
귀하의 특정 질문에 관해서 :
이것의 이유는 무엇입니까?
를 들어, 트램 폴린 등 E48 : 코드는 사용자의 범위 E4의 첫 번째 셀을 사용하는 점프 아래로 End(xlDown)
.
“오차”출력은 아마도 첫 번째 이외의 범위에 비어 있지 않은 셀 이없는 경우 얻을 수 있습니다 . 그런 다음 어두운 곳 으로 뛰어 들어 갑니다. 즉, 워크 시트를 아래로 내립니다 ( 공백 과 빈 문자열 의 차이점에 주목해야 합니다 !).
참고 :
-
범위에 연속되지 않은 비 공백 셀이 포함 된 경우 잘못된 결과도 제공됩니다.
-
비어 있지 않은 셀이 하나만 있지만 첫 번째 셀이 아닌 경우 코드에서 여전히 올바른 결과를 제공합니다.
답변
마지막 행, 열 및 셀 을 결정하기 위해이 원 스톱 함수를 만들었습니다. 데이터, 형식화 (commented / commented / hidden) 셀 또는 조건부 서식 입니다.
Sub LastCellMsg()
Dim strResult As String
Dim lngDataRow As Long
Dim lngDataCol As Long
Dim strDataCell As String
Dim strDataFormatRow As String
Dim lngDataFormatCol As Long
Dim strDataFormatCell As String
Dim oFormatCond As FormatCondition
Dim lngTempRow As Long
Dim lngTempCol As Long
Dim lngCFRow As Long
Dim lngCFCol As Long
Dim strCFCell As String
Dim lngOverallRow As Long
Dim lngOverallCol As Long
Dim strOverallCell As String
With ActiveSheet
If .ListObjects.Count > 0 Then
MsgBox "Cannot return reliable results, as there is at least one table in the worksheet."
Exit Sub
End If
strResult = "Workbook name: " & .Parent.Name & vbCrLf
strResult = strResult & "Sheet name: " & .Name & vbCrLf
'DATA:
'last data row
If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
lngDataRow = .Cells.Find(What:="*", _
After:=.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
Else
lngDataRow = 1
End If
'strResult = strResult & "Last data row: " & lngDataRow & vbCrLf
'last data column
If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
lngDataCol = .Cells.Find(What:="*", _
After:=.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
Else
lngDataCol = 1
End If
'strResult = strResult & "Last data column: " & lngDataCol & vbCrLf
'last data cell
strDataCell = Replace(Cells(lngDataRow, lngDataCol).Address, "$", vbNullString)
strResult = strResult & "Last data cell: " & strDataCell & vbCrLf
'FORMATS:
'last data/formatted/grouped/commented/hidden row
strDataFormatRow = StrReverse(Split(StrReverse(.UsedRange.Address), "$")(0))
'strResult = strResult & "Last data/formatted row: " & strDataFormatRow & vbCrLf
'last data/formatted/grouped/commented/hidden column
lngDataFormatCol = Range(StrReverse(Split(StrReverse(.UsedRange.Address), "$")(1)) & "1").Column
'strResult = strResult & "Last data/formatted column: " & lngDataFormatCol & vbCrLf
'last data/formatted/grouped/commented/hidden cell
strDataFormatCell = Replace(Cells(strDataFormatRow, lngDataFormatCol).Address, "$", vbNullString)
strResult = strResult & "Last data/formatted cell: " & strDataFormatCell & vbCrLf
'CONDITIONAL FORMATS:
For Each oFormatCond In .Cells.FormatConditions
'last conditionally-formatted row
lngTempRow = CLng(StrReverse(Split(StrReverse(oFormatCond.AppliesTo.Address), "$")(0)))
If lngTempRow > lngCFRow Then lngCFRow = lngTempRow
'last conditionally-formatted column
lngTempCol = Range(StrReverse(Split(StrReverse(oFormatCond.AppliesTo.Address), "$")(1)) & "1").Column
If lngTempCol > lngCFCol Then lngCFCol = lngTempCol
Next
'no results are returned for Conditional Format if there is no such
If lngCFRow <> 0 Then
'strResult = strResult & "Last cond-formatted row: " & lngCFRow & vbCrLf
'strResult = strResult & "Last cond-formatted column: " & lngCFCol & vbCrLf
'last conditionally-formatted cell
strCFCell = Replace(Cells(lngCFRow, lngCFCol).Address, "$", vbNullString)
strResult = strResult & "Last cond-formatted cell: " & strCFCell & vbCrLf
End If
'OVERALL:
lngOverallRow = Application.WorksheetFunction.Max(lngDataRow, strDataFormatRow, lngCFRow)
'strResult = strResult & "Last overall row: " & lngOverallRow & vbCrLf
lngOverallCol = Application.WorksheetFunction.Max(lngDataCol, lngDataFormatCol, lngCFCol)
'strResult = strResult & "Last overall column: " & lngOverallCol & vbCrLf
strOverallCell = Replace(.Cells(lngOverallRow, lngOverallCol).Address, "$", vbNullString)
strResult = strResult & "Last overall cell: " & strOverallCell & vbCrLf
MsgBox strResult
Debug.Print strResult
End With
End Sub
결과는 다음과 같습니다.
보다 자세한 결과를 얻으려면 코드의 일부 줄을 주석 처리 해제 할 수 있습니다.
한 가지 제한 사항이 있습니다-시트에 테이블이 있으면 결과를 신뢰할 수 없게 될 수 있으므로이 경우 코드를 실행하지 않기로 결정했습니다.
If .ListObjects.Count > 0 Then
MsgBox "Cannot return reliable results, as there is at least one table in the worksheet."
Exit Sub
End If
답변
솔루션을 사용할 때 명심해야 할 중요한 사항 …
LastRow = ws.Cells.Find(What:="*", After:=ws.range("a1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
… LastRow
변수가 다음 Long
유형 인지 확인하는 것 입니다 .
Dim LastRow as Long
그렇지 않으면 .XLSX 통합 문서의 특정 상황에서 OVERFLOW 오류가 발생합니다.
이것은 다양한 코드 사용에 빠지는 캡슐화 된 함수입니다.
Private Function FindLastRow(ws As Worksheet) As Long
' --------------------------------------------------------------------------------
' Find the last used Row on a Worksheet
' --------------------------------------------------------------------------------
If WorksheetFunction.CountA(ws.Cells) > 0 Then
' Search for any entry, by searching backwards by Rows.
FindLastRow = ws.Cells.Find(What:="*", After:=ws.range("a1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
End If
End Function
답변
Siddarth Rout이 제공 한 답변에 행 번호 대신 Range 개체 반환 반환을 사용하여 CountA 호출을 건너 뛸 수 있다고 말한 다음 반환 된 Range 개체를 테스트하여 아무것도 없는지 확인하십시오 (빈 워크 시트) .
또한 LastRow 프로 시저의 버전에서 빈 워크 시트에 대해 0을 반환하면 비어 있음을 알 수 있습니다.
답변
아무도 이것을 언급하지 않았지만, 마지막으로 사용한 셀을 얻는 가장 쉬운 방법은 다음과 같습니다.
Function GetLastCell(sh as Worksheet) As Range
GetLastCell = sh.Cells(1,1).SpecialCells(xlLastCell)
End Function
이것은 본질적으로 Cell을 선택한 후 Ctrl+ 와 동일한 셀을 반환합니다 .EndA1
주의 사항 : Excel은 워크 시트에 사용 된 가장 오른쪽 아래 셀을 추적합니다. 예를 들어 B3에 무언가를 입력하고 H8에 무언가 를 입력 한 다음 나중에 H8 의 내용을 삭제하면 Ctrl+ End를 누르면 여전히 H8 셀로 이동합니다. 위의 기능은 동일한 동작을합니다.
답변
원래 질문은 마지막 셀을 찾는 문제 에 관한 것이기 때문에이 답변에는 예기치 않은 결과를 얻을 수있는 다양한 방법이 나열되어 있습니다 . “매크로를 사용하여 Excel 시트의 데이터가 포함 된 마지막 행을 어떻게 찾을 수 있습니까?”에 대한 내 답변을 참조하십시오. 이 문제를 해결하기 위해 노력했습니다.
나는에 확대하여 시작합니다 sancho.s로 대답 하고 GlennFromIowa에 의해 코멘트 도 자세한 내용을 추가 :
[…] 먼저 어떤 것이 사용되는지를 결정해야합니다. 나는 적어도 6 개의 의미를 봅니다. 세포는 :
- 1) 데이터, 즉 공식은 가능한 빈 값을 초래한다.
- 2) 값, 즉 비 공백 공식 또는 상수;
- 3) 서식;
- 4) 조건부 서식;
- 5) 셀과 겹치는 모양 (주석 포함);
- 6) 테이블에 참여 (목록 객체).
어떤 조합을 테스트 하시겠습니까? 일부 (예 : 표)는 테스트하기가 더 어려울 수 있으며 일부는 데이터 범위를 벗어난 모양과 같이 드물지만 상황에 따라 달라질 수 있습니다 (예 : 빈 값이있는 수식).
고려해야 할 다른 것들 :
- A) 숨겨진 행 (예 : 자동 필터), 빈 셀 또는 빈 행이있을 수 있습니까?
- B) 어떤 종류의 성능이 허용됩니까?
- C) VBA 매크로가 통합 문서 또는 응용 프로그램 설정에 어떤 방식으로 영향을 줄 수 있습니까?
이를 염두에두고 “마지막 셀”을 얻는 일반적인 방법이 어떻게 예기치 않은 결과를 생성 할 수 있는지 살펴 보겠습니다 .
.End(xlDown)
질문 의 코드는 Siddharth Rout의 답변에 설명 된 이유 때문에 (예 : 하나의 비어 있지 않은 단일 셀 또는 사이에 빈 셀이있는 경우) 가장 쉽게 중단 됩니다 ( “xlDown은 똑같이 신뢰할 수 없습니다” ) .Count
ing (CountA
또는Cells*.Count
)을 기반으로 하거나.CurrentRegion
빈 셀이나 행이있을 때 중단되는 솔루션 👎.End(xlUp)
CTRL + UP과 마찬가지로 열 끝에서 뒤로 검색 하는 솔루션 은 보이는 행 에서 데이터 (공백 값을 생성하는 수식은 “데이터”로 간주 됨)를 찾습니다 (자동 필터를 사용하여 사용하면 잘못된 결과가 발생할 수 있음) ).마지막 행 하드 코딩과 같은 표준 함정을 피하기 위해주의를 기울여야합니다 (자세한 내용 은 Siddharth Rout의 답변을 여기에서 다시 참조하십시오 . “열에서 마지막 행 찾기” 섹션 참조
Range("A65536").End(xlUp)
). 에 의존하는 대신sht.Rows.Count
..SpecialCells(xlLastCell)
CTRL + END와 동일하며 “사용 된 범위”의 맨 아래 및 가장 오른쪽 셀을 반환하므로 “사용 된 범위”에 의존하는 모든 경고는이 방법에도 적용됩니다. 또한 통합 문서를 저장할 때, “사용 된 범위는”단지 재설정 및 액세스 할 때worksheet.UsedRange
, 그래서xlLastCell
저장되지 않은 변경과 부실 results⚠️을 생산하고 있습니다 (예를 들어, 일부 행이 삭제 된 후). dotNET 근처의 답변을 참조하십시오 .sht.UsedRange
( 여기 에서 sancho.s의 답변 에 자세히 설명되어 있음 ) 는 데이터와 서식 (조건부 서식은 아니지만 )을 모두 고려 하고 워크 시트의 “사용 범위”를 재설정합니다 .일반적인 실수 는 마지막 행 번호가 아닌 사용 된 범위 의 행 수
.UsedRange.Rows.Count
를 반환하는 ⚠️을 사용 하는 것입니다 (처음 몇 행이 비어 있으면 다를 수 있음). 자세한 내용은 어떻게 찾을 수 있는지에 대한 newguy의 답변을 참조하십시오 매크로가있는 Excel 시트의 데이터가 포함 된 마지막 행?.Find
데이터 (공식 포함) 또는 모든 열에서 공백이 아닌 값 으로 마지막 행을 찾을 수 있습니다 . 수식이나 값에 관심이 있는지 여부를 선택할 수 있지만 Excel의 찾기 대화 상자 ️️⚠️ 에서 기본값을 재설정 하여 사용자에게 혼란을 줄 수 있습니다. 또한 신중하게 사용해야합니다. 여기 Siddharth Rout의 답변을 참조 하십시오 ( “시트의 마지막 행 찾기” 섹션) )Cells
루프에서 개별 ‘ 를 검사하는보다 명확한 솔루션 은 일반적으로 Excel 함수를 다시 사용하는 것보다 느리지 만 (아직 성능이있을 수는 있지만) 원하는 것을 정확하게 지정할 수 있습니다. 주어진 열에 데이터가있는 마지막 셀을 찾으려면 내 솔루션 기반UsedRange
및 VBA 배열을 참조하십시오. 숨겨진 행, 필터, 공백을 처리하고 찾기 기본값을 수정하지 않으며 성능이 뛰어납니다.
어떤 솔루션을 선택하든 조심하십시오
- 행 번호를 저장하는
Long
대신 사용Integer
(Overflow
65k 개 이상의 행 을 사용하지 않도록 ) - 항상 작업중 인 워크 시트를 지정하십시오 (예 :
Dim ws As Worksheet ... ws.Range(...)
대신Range(...)
) - 사용하는 경우
.Value
이다 (이는Variant
) 등 암시 캐스트 방지.Value <> ""
셀 에러 값을 포함하는 경우가 실패로한다.