Find out Last Non Empty in a Column Range

**pintusolanki1** · 06-04-2017, 04:59 PM

Hi,
i have List B8:E37 generated using data validation table.in Column E, i have use formula as =IFERROR(INDEX(Choices!$F$2:$F$90,SMALL(IF(($C$4=Choices!$F$2:$F$90)*IF($D$4="ALL",1,($D$4=Choices!$G$2:$G$90)),ROW(Choices!$F$2:$F$90)-ROW(Choices!$F$2)+1,""),ROWS(Choices!$F$2:F2))),"") in which if the data is not present it will added "" in that cell of E Column.

E8:E24 contains data whereas E25:E37 is displayed as blank but formula is there.

Since in my case E24 is the last Non Empty Cell. but when i use formula to find out Last Column number I get as 37.

pl help to find out last Non Empty Cell.

below is the code i used to verify

=IF(E25="",TRUE,FALSE) ==> True

Below is the code which i am using

Dim ws As Worksheet
Dim LastCell As Range
Dim LastCellRowNumber As Long

Set ws = Worksheets("Input_Sheet")
With ws
    Set LastCell = .Cells(.Rows.Count, "E").End(xlUp)
    LastCellRowNumber = LastCell.Row
    
End With

**JLGWhiz** · 06-04-2017, 05:15 PM

try

Dim lastRow As Long
With ActiveSheet
    lastRow = .Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
End With

**pintusolanki1** · 06-04-2017, 05:36 PM

Great! it is Working. i really appreciate your support

With ws.Columns(5)
 lastRow = .Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
 End With

**TMS** · 06-04-2017, 05:33 PM

See Test3 below

Option Explicit

Sub test()
Test1
Test2
Test3
End Sub

Sub Test1()
Dim ws As Worksheet
Dim LastCell As Range
Dim LastCellRowNumber As Long

Set ws = Worksheets("Input_Sheet")
With ws
    Set LastCell = .Cells(.Rows.Count, "E").End(xlUp)
    LastCellRowNumber = LastCell.Row
    
End With
Debug.Print LastCellRowNumber
End Sub

Sub Test2()
Dim lastRow As Long
With ActiveSheet
    lastRow = .Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
End With
Debug.Print lastRow
End Sub

Sub Test3()
Dim lLR As Long
lLR = Application.Evaluate("=Match("""", E:E, 0)-1")
Debug.Print lLR
End Sub

Find out Last Non Empty in a Column Range

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Find out Last Non Empty in a Column Range

Re: Find out Last Non Empty in a Column Range

Re: Find out Last Non Empty in a Column Range

Re: Find out Last Non Empty in a Column Range

Thread Information

Users Browsing this Thread

Similar Threads

Find the next non-empty cell in column range

[SOLVED] How do I find the first empty cell in a range of cells in a column

[SOLVED] Find next empty cell (Row)within a specific range in column A

Find empty cell in column, and apply required character to empty visable cells.

Need to find empty filled cells in a column and copy to the empty cell below

How to find the first Non Empty Column & row in a range

Macro to Find any empty cell within a range and show result in another column

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions