XL Last Cell 다루기

20201007 1547 

 

Ref

[1] "dmcritchie.mvps.org/excel/lastcell.htm" , more specific and expert-like

[2] "exceljet.net/formula/get-value-of-last-non-empty-cell"

[3] "www.excelcampus.com/vba/find-last-row-column-cell/"

 

01 Using VBA [3]

01.01 Range.SpecialCells()

sub test()
lastcell=Sheets(1).UsedRange.SpecialCells(xlLastCell).Address
Sheets(1).Range("a4",lastcell).ClearContents
ens sub

* SpecialCells method is the same as press "Ctrl+End" in an Excel sheet.

* Range("a1").SpecialCells(xlCellTypeLastCell)

 

01.02 Range.End()

- It's useful when you're looking the last cell in ONLY ONE colum or row.

zRow=Cells(Rows.Count,1).End(xlUp).Row  'Find the last non-blank cell in column A zCol=Cells(1,Columns.Count).End(xlToLeft).Column  'Find the last non-blank cell in row 1

Sub test()
  MsgBox Cells(Rows.Count, 1).End(xlUp).Row
  MsgBox Cells(1, Columns.Count).End(xlToLeft).Column
End Sub

★ meaning of "Rows.Count"

Run this code : MagBox Rows.Count. The result is below.

 

01.03 Range.Find()

★ The Range.Find method is the same as Excel's Find Window

 

01.04 Range columns count; 직관적인 방법

Sub test()     zrow = Sheets(1).UsedRange.Rows.Count     zcol = Sheets(1).UsedRange.Columns.Count     Range("b2", Cells(zrow, zcol)).Select End Sub

 

02 Using Excel Formula

02.01 Get value of last non-empty cell [2]

=LOOKUP(2,1/(A:A<>""),A:A)

syntex : LOOKUP(lookup_vlaue, lookup_vector, result_vector)

★ it's better than vlookup

 

(explanation) 

① A:A<> "" returns {true,false, true, ...}

② 1/true → 1, 1/false → #DIV/0!

③  the lookup_value is 2, 

★ lookup함수는 일치하는 값 중에 맨 나중에 값(의 위치)를 돌려준다.

일치하는 값이 없으면 찾는 값보다 "다음의 작은 값"을 찾는다.

④ 따라서 배열의 두 값(1, #DIV/0!) 중에서 2보다 다음으로 작은 값(=1)을 찾고, 그 중 맨 마지막 위치를 알려준다.

 

02.02 Dealing with errors

 

02.03 Last numeric value

 

02.04 Position of the last value

=LOOKUP(2,1/(A:A<>"",ROW(A:A))