Solved

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

Posted on 2013-11-18
7
253 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
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 
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 500 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

Get HTML5 Certified

Want to be a web developer? You'll need to know HTML. Prepare for HTML5 certification by enrolling in July's Course of the Month! It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

628 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