Last Row Function Issue

[rjacmuto32]

I'm having an issue with declaring the last row # in my macro. I have a macro with this and it works but it doens't work 100% the way I want it to.

I have this to get my last row.
LastA = Range("D21").End(xlDown).Row

Then I have this to select some cells and do some other functions that work great.
Range("D21" & ":D" & LastA).Select
more code
Range("F21" & ":F" & LastA).Select

The problem is Column D has formula's linked to another worksheet. There is an IF formula to show Blank in say D30. My last A declaration above using XLDown is going to the bottom of my range every time at row 35 since there is a formula in there but it just shows me blank. I only want to declare my LastA be the row that shows me the text and not the blank via the formula. So if cell D27 is blank the lastA shouuld be row 26 and not the very bottom of cell 35. Any idea how i can change up

LastA = Range("D21").End(xlDown).Row
to take into account for the cells having a formula but showing me blank that i can use.

Thanks,
--Robert

 

[Somendra Misra]

Hi Robert,

Try something like this.

Sub selectlastrow()
 
lasta = Range("D21").End(xlDown).Row
 
lastA1 = Application.WorksheetFunction.CountIf(Range("D21:D" & lasta), "")
 
lastrow = lasta - lastA1
 
Range("D21" & ":D" & lastrow).Select
 
 
End Sub

Regards,

 

[NARAYANK991]

Hi Robert ,

Add this line :

lastA1 = Evaluate("=MAX(IF(D21:D" & lastA & "="""",ROW(D2:D" & lastA & ")))")-1

lastA1 will give the last row just before the blank.

Narayan