Go Premium for a chance to win a PS4. Enter to Win

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
?
265 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
  • 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 Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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!
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

824 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