Solved

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

Posted on 2013-11-18
228 Views
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)
``````
Thanks,
John
0
Question by:gabrielPennyback
• 4
• 3

LVL 12

Expert Comment

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
``````
0

LVL 1

Author Comment

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

ID: 39660249
John,

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

This will help you to find the last column of any rows that contains Monday in column A.
0

LVL 12

Expert Comment

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

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

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

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

Question has a verified solution.

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

### Suggested Solutions

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

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

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