Code or formula that will find the last column of rows with a specified value

There are a lot of ways to find the last column used in a worksheet, but I need to find the last column used in all the rows that contain the value "Monday" (or whatever) in the fourth column.

How do I modify this code to define my range as all rows with "Monday" in Column D?

Dim rLastCell As Range
Set rLastCell = Rows("6:1708").Find(What:="*", After:=Cells(6, 1), _
LookIn:=xlValues, LookAt:=xlPart, SearchDirection:=xlPrevious)

Open in new window

Thanks,
John
LVL 1
gabrielPennybackReliability Business Tools Analyst IIAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Harry LeeConnect With a Mentor Commented:
John,

Is the table you sent me the Helper Grid you are referring to?

If it is, this is not going to help me at al.

I need the lowest level of data (Raw data). In order to figure out a way to get the date you want.

Give me some dummy data to figure out the formula or VBA Macro.

Harry
0
 
Harry LeeCommented:
Use the following macro

Sub FindLast()
    Dim Result As Range, SearchString As String
    SearchString = InputBox("Please enter search string")
    Set Result = Range("D:D").Find(What:=SearchString, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)
    If Not Result Is Nothing Then
        MsgBox "The last instance of """ & SearchString & """ is in cell " & Result.Address
    Else
        MsgBox "No """ & SearchString & """ is found"
    End If
End Sub

Open in new window

0
 
gabrielPennybackReliability Business Tools Analyst IIAuthor Commented:
Hi Harry, thanks for posting. As is often the case, I didn't pose my question very clearly.  What I need in theory is t replace:
     Rows("6:1708")
with
     Only those rows in Rows("6:1708") which have "Monday" in Column A.

If I didn't have 65 different strings to find results for, I would just create a helper column in Column IV and populate all the rows that meet the "Monday" criteria with x's and then define my range as:
Range("IV78:IV1708").SpecialCells(xlCellTypeConstants)

So I'm not looking to find a cell in Column D. What I'm looking for is this: if 32 cells in the Range("D7:D1708") contain Monday, I want to know which one of those 32 rows has the  farthest right column with data in it.

I hope that's a little clearer.

Thanks,
John
0
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
Harry LeeCommented:
John,

What about this code?

Option Explicit
Sub FindLongestRowWithSpecificText()
Dim Rw As Long, SearchString As String, Arr() As Variant, ArrRw As Long, ArrClmn As Long, I As Long
Dim MaxLength As Long, MaxAddress As String

ArrClmn = 1
ArrRw = 0

SearchString = InputBox("Please Enter Search String")

For Rw = 1 To Cells(Rows.Count, 1).End(xlUp).Row
    If Cells(Rw, 1) = SearchString Then
        ReDim Preserve Arr(ArrClmn, ArrRw)
        Arr(0, ArrRw) = Cells(Rw, Columns.Count).End(xlToLeft).Column
        Arr(ArrClmn, ArrRw) = Cells(Rw, Columns.Count).End(xlToLeft).Address
        ArrRw = ArrRw + 1
    End If
Next

MaxLength = 0
MaxAddress = ""
For I = 1 To UBound(Arr, 2)
    If Arr(0, I) > MaxLength Then
        MaxLength = Arr(0, I)
        MaxAddress = Arr(1, I)
    End If
Next

MsgBox ("The Longest Row, that Column A contains " & SearchString & " is located at " & MaxAddress)

End Sub

Open in new window


This will help you to find the last column of any rows that contains Monday in column A.
0
 
Harry LeeCommented:
John,

Do you mind to post a sample file and state clearly what you are ultimately wanting to achieve?

There may be much more straight forward solutions to it rather than dealing with tiny pieces of your problem.
0
 
gabrielPennybackReliability Business Tools Analyst IIAuthor Commented:
Henry, this should give you a pretty good idea of what I'm trying to accomplish. I actually have come up with something that works, but since I have 67 customers and 1700+ rows ,it required a helper grid of 114,000 cells which I'd like to eliminate!

Thanks,
John
LastColumnFinder.xlsm
0
 
gabrielPennybackReliability Business Tools Analyst IIAuthor Commented:
Hi Henry, I've been so distracted with other things and still am, so I'm awarding you the points and when I have time I'll repost the question.

Thanks,
John
0
All Courses

From novice to tech pro — start learning today.