Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2013-11-18
7
Medium Priority
?
260 Views
Last Modified: 2013-12-10
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
0
Comment
Question by:gabrielPennyback
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 12

Expert Comment

by:Harry Lee
ID: 39658172
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
 
LVL 1

Author Comment

by:gabrielPennyback
ID: 39658202
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
 
LVL 12

Expert Comment

by:Harry Lee
ID: 39660249
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 12

Expert Comment

by:Harry Lee
ID: 39660343
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
 
LVL 1

Author Comment

by:gabrielPennyback
ID: 39663843
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
 
LVL 12

Accepted Solution

by:
Harry Lee earned 1500 total points
ID: 39664149
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
 
LVL 1

Author Closing Comment

by:gabrielPennyback
ID: 39710257
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question