Find last Row in VBA ignoring formula blanks...

[Matty]

Hi,

I have a data set where I want to establish the last Row using Column A. For this, I would normally use:

LR = Range("A" & Rows.Count).End(xlUp).Row

However, the data set I am working on contains formula blanks ("") after the last "real" value, and this method is taking these Rows into account which I don't want.

Does anyone know an alternative method of doing this, where the formula blanks are ignored?

Thanks,

Matty

 

[jc.021286]

Loopingsub()

Set a = sheets(1).Cells(1,1) 'start at cell A1
While a <> ""
a = a.offset(1,0)
Wend
a.select

then using your .end(xlup) would work.

This however will fail for your purposes if you have any blanks in your data.
Otherwise you could reverse it to start at the bottom and loop up until it finds the first non "" data point.

please let me know if this is helpful,
jc

 

[VoG]

Try

LR = Columns("A").Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues).Row

 

[Matty]

Thanks to both.

I've gone with Peter's suggestion to avoid the looping.

Cheers,

Matty

 

[Jonty12]

Try

LR = Columns("A").Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues).Row

Hi,

Thanks VoG, for the solution to a problem I've been having.

I just wanted to point something out;

When the format of the column is set to Accounting, the code seems to get the last formula row instead of the last formula result.
ie. Formula with no result at row 82, formula with a result at 31 ; the code will return 82

Setting the column to General, Number of Currency seems to work for obtaining the last formula result.

I'm just wondering why it isn't working with Accounting format?

I can work around this, but maybe theres a slight change that can be made to that line of code.
I would have thought 'LookIn:=xlValues' would have done it.



Jonty

 

[VoG]

Sorry, I don't know why the cell format would affect that.

 

[pgc01]

Hi Jonty
Welcome to the board

Try:

LR = [match(2,1/(a:a<>""))]

 

[Jonty12]

Sorry, I don't know why the cell format would affect that.

Hi VoG,
No worries. You weren't to know that at the time.
I just thought it was worth joining up to the forum to point that out. And I use these forums quite alot

I worked around it with the currency format at my last post.

Hi Jonty
Welcome to the board

Try:

LR = [match(2,1/(a:a<>""))]

Hi pgc,
Thanks for the welcome .

I've only just been able to test your code, and it now works with accounting format.

Here is the code with your ammendments: (roughly in the middle of the code window)

Sub NextDay2()

Dim WS As Worksheet
Dim LastCellRowNumber As Long

Dim LastCellD As String
Dim LastCellE As String
Dim LastCellF As String
Dim LastCellG As String
Dim LastCellH As String
Dim LastCellI As String
Dim LastCellJ As String
Dim LastCellK As String
Dim LastCellL As String
Dim LastCellM As String
Dim LastCellN As String
Dim LastCellO As String
Dim LastCellP As String
Dim LastCellQ As String

Dim i As Integer
Dim TheDay As Date
Dim wSheet As String

wSheet = ActiveSheet.Name
i = 0
    
Application.ScreenUpdating = False
        
TheDay = Range("E65536").End(xlUp).Offset(0, 0).Value
TheDay = TheDay + 1


Set WS = Worksheets(wSheet)
With WS
    LastCellD = [match(2,1/(D:D<>""))]
    LastCellE = [match(2,1/(E:E<>""))]
    LastCellF = [match(2,1/(F:F<>""))]
    LastCellG = [match(2,1/(G:G<>""))]
    LastCellH = [match(2,1/(H:H<>""))]
    LastCellI = [match(2,1/(I:I<>""))]
    LastCellJ = [match(2,1/(J:J<>""))]
    LastCellK = [match(2,1/(K:K<>""))]
    LastCellL = [match(2,1/(L:L<>""))]
    LastCellM = [match(2,1/(M:M<>""))]
    LastCellN = [match(2,1/(N:N<>""))]
    LastCellO = [match(2,1/(O:O<>""))]
    LastCellP = [match(2,1/(P:P<>""))]
    LastCellQ = [match(2,1/(Q:Q<>""))]

'---------------------------------------------------------------------------------------------------------------------
' THIS LINE (for each variable) WASN'T WORKING FOR ACCOUNTING FORMAT 
'*****LastCellQ = Columns("Q").Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues).Row
'---------------------------------------------------------------------------------------------------------------------
    
    LastCellRowNumber = Application.WorksheetFunction.Max(LastCellD, LastCellE, LastCellF, LastCellG, LastCellH, LastCellI, LastCellJ _
    , LastCellK, LastCellL, LastCellM, LastCellN, LastCellO, LastCellP, LastCellQ)
End With

LastCellRowNumber = LastCellRowNumber + 1
    
    
    Range("E" & LastCellRowNumber).Value = TheDay

        
    Range("E65536").End(xlUp).Offset(0, -1).Select

    Do
    
        'Top Border thick (Weight 1-4)
        With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = 3
        End With
        ActiveCell.Offset(0, 1).Select
        i = i + 1
        
    Loop Until i = 14
        
    Application.ScreenUpdating = True
    
    ActiveWindow.ScrollColumn = 1
    Range("E65536").End(xlUp).Offset(0, 1).Select

End Sub

Basically my code is placing the next day on an employees time sheet, where the lastrow with data may be in a different column.
- hence I need to check all columns for the lastrow, and then see which is the max.

Then at the end, I'm just drawing a line across to seperate the days.

Thanks pgc

 

[pgc01]

Hi Jonty

I'm glad it helped, but ...

1 -
you're lucky that it worked, because the syntax is wrong.

Set ws = Worksheets("Sheet2")
    LastCellD = [match(2,1/(a:a<>""))]

The second statement has nothing to do with the first one, in the second one you are evaluating the formula in the active sheet, which is not what you want.

To evaluate the expression in worksheet ws, you must qualify the evaluate:

Set ws = Worksheets("Sheet2")
    LastCellD = ws.[match(2,1/(a:a<>""))]

In this specific case, since ws is the reference to the active sheet it works, but if ws would reference another worksheet it would not work.

2 -

With WS
    LastCellD = [match(2,1/(D:D<>""))]
    LastCellE = [match(2,1/(E:E<>""))]
    LastCellF = [match(2,1/(F:F<>""))]
    LastCellG = [match(2,1/(G:G<>""))]
    LastCellH = [match(2,1/(H:H<>""))]
    LastCellI = [match(2,1/(I:I<>""))]
    LastCellJ = [match(2,1/(J:J<>""))]
    LastCellK = [match(2,1/(K:K<>""))]
    LastCellL = [match(2,1/(L:L<>""))]
    LastCellM = [match(2,1/(M:M<>""))]
    LastCellN = [match(2,1/(N:N<>""))]
    LastCellO = [match(2,1/(O:O<>""))]
    LastCellP = [match(2,1/(P:P<>""))]
    LastCellQ = [match(2,1/(Q:Q<>""))]

    LastCellRowNumber = Application.WorksheetFunction.Max(LastCellD, LastCellE, LastCellF, LastCellG, LastCellH, LastCellI, LastCellJ _
    , LastCellK, LastCellL, LastCellM, LastCellN, LastCellO, LastCellP, LastCellQ)

You want the last row in columns D:Q

You could use instead:

Dim r As Range
'...
With ws
    
    Set r = Intersect(ws.UsedRange, ws.Columns("D:Q"))
    LastCellRowNumber = ws.Evaluate("max(if(" & r.Address & "<>"""",row(" & r.Address & ")))")

 

[Jonty12]

Hi pgc,

Thanks for taking the time to explain.

I understand what you mean in regards to qualifying the ws. I just didn't realise this, and am glad you saw it.
I'll edit and make a note of that in the file.

I'll give that other code you've provided a go. That looks alot easier :D

Thanks very much
Regards
Jonty