Mac Excel 2011, how to find last row in column with data?

[vanclute]

I've been scouring the interwebz only to find loads of solutions that worked years ago, work on Windows, or for some reason don't seem to do a darn thing for me. Could be I'm making mistakes, wouldn't be the first time. But it also wouldn't be the first time an issue was specific to Mac Excel 2011!

So I'm asking here... how can I use VBA to find the last row in column E, that is not blank and actually contains data (not just a formula that is producing a blank result currently)?

 

[Trebor76]

Hi vanclute,

Here's the code I'd use with a PC:

Option Explicit
Sub Macro6()

    Dim lngMyRow As Long
    Dim lngLastRow As Long
    
    Application.ScreenUpdating = False
    
    lngLastRow = Cells(Rows.Count, "E").End(xlUp).Row 'Finds the last row used in Col. E
    
    For lngMyRow = lngLastRow To 1 Step -1
        If Len(Range("E" & lngMyRow)) > 0 Then
            Exit For
        End If
    Next lngMyRow
    
    MsgBox lngMyRow
    
    Application.ScreenUpdating = True

End Sub

The lngMyRow variable is what you're after. Hopefully converting it for Excel on a Mac won't be too problematic for you.

Regards,

Robert

 

[JoeMo]

I've been scouring the interwebz only to find loads of solutions that worked years ago, work on Windows, or for some reason don't seem to do a darn thing for me. Could be I'm making mistakes, wouldn't be the first time. But it also wouldn't be the first time an issue was specific to Mac Excel 2011!

So I'm asking here... how can I use VBA to find the last row in column E, that is not blank and actually contains data (not just a formula that is producing a blank result currently)?

Robert's formula, which is equivalent to the formula for lastRwEither in the code below, will find the last row even if it happens to contain a formula that is returning "" (a zero-length string). Form your post I suspect you want to find the last row with data. That's lastRwData in the code below.

Sub FindLastRow()
'Assume col E
Dim lastRwEither As Long, lastRwData As Long, lastRwFormula As Long
'Next line will find last row in col  Ewith data or a formula that returns ""
lastRwEither = Range("E" & Rows.Count).End(xlUp).Row
MsgBox "lastRwEither =  " & lastRwEither
'Next line will find last row in col E with data but ignores cells with formula that returns ""
lastRwData = Range("E:E").Find(what:="*", after:=Range("E" & Rows.Count), LookIn:=xlValues, searchdirection:=xlPrevious).Row
MsgBox "lastRwData = " & lastRwData
'Next line will find last row if it has a formula that returns ""
lastRwFormula = Range("E:E").Find(what:="*", after:=Range("E" & Rows.Count), LookIn:=xlFormulas, searchdirection:=xlPrevious).Row
MsgBox "lastRwFormula = " & lastRwFormula
End Sub

 

[vanclute]

The lngMyRow variable is what you're after. Hopefully converting it for Excel on a Mac won't be too problematic for you.

Thanks, that worked just fine as is! But now how do I select that row? Ultimately I need to copy its contents for pasting elsewhere. The approaches I've tried for selecting the cell once found, haven't worked.

 

[Trebor76]

Hi Joe,

Hope this finds you well. Not sure if I fully understood your post but my code also ignores zero-length strings

Hi vanclute,

Just use the lngMyRow variable as needed i.e. to select the row try this:

Rows(lngMyRow).EntireRow.Select

Keep in mind though that you do not need to select the row to copy it.

HTH

Robert

 

[mikerickson]

This should work. Notice the xlPrevious and xlValues arguments.

Dim lastRow As Long

With Range("E:E")
    lastRow = .Find(What:="?*", After:=.Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, MatchCase:=False).Row
End With

 

[vanclute]

Just use the lngMyRow variable as needed i.e. to select the row try this:

Rows(lngMyRow).EntireRow.Select

I only want the actual value in the single cell though, not the entire row. I mixed up the terminology in my previous post, saying row once and cell once. Sorry bout that!

Keep in mind though that you do not need to select the row to copy it.

Hmmm good point, I actually did "know" that from my reading, but hadn't thought about actually applying it. I'll continue to experiment to see if I can figure out how to make it work. Thanks!

 

[vanclute]

I'm clearly doing something wrong here. Along with much other way more esoteric and unlikely-to-work experimentations, I've tried the simpler:

Range(lngMyRow).Select

to simply select the cel in question, and

Range(lngMyRow).Copy

to copy it directly without selecting first. Neither work, resulting in a runtime error. How can I go about copying the resulting cell?

 

[vanclute]

Got it! End result:

Range("F" & lngMyRow).Value = Range("E" & lngMyRow).Value

Does exactly what I needed. Thanks Robert & all!

 

[Trebor76]

Thanks Robert & all!

You're welcome. I'm glad you got it sorted in the end