+ Reply to Thread
Results 1 to 4 of 4

Find Last Column with Data in Specific Row based on text

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    09-13-2008
    Location
    Los Angeles, CA
    MS-Off Ver
    365
    Posts
    120

    Find Last Column with Data in Specific Row based on text

    I'm trying to find the row that has "Net Due:" in it and then find the last column in that row with data (not the first empty row, as you could have empty rows and then data) and then highlight that range.

    I got the below to work on a test sheet but has an error on the actual ones I need it on and I can't figure out why I'm getting the following error on 'LastCol =' row, Run-time error '91': Object variable or With block variable not set

    I'm sure it's something silly but I've spent too long staring at it, thanks in advance!


    Dim FindRow As Range
    Dim LastCol As Long
    
        Set FindRow = Range("A:A").Find(What:="Net Due:", LookIn:=xlValues)
        
        LastCol = Cells(FindRow.Row, Columns.Count).End(xlToLeft).Column
        
        Range(Cells(FindRow.Row, 1), Cells(FindRow.Row, LastCol)).Select
        With Selection.Interior
            .Color = 5296274
        End With

  2. #2
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MSO Home and Business 2024
    Posts
    7,363

    Re: Find Last Column with Data in Specific Row based on text

    This works fine for me.

    Sub tst()
        Dim fRow, lastcol As Long
        fRow = Application.Match("Net Due:", Columns(1), 0)
        If Not IsError(fRow) Then
            lastcol = Cells(fRow, Columns.Count).End(xlToLeft).Column
            Cells(fRow, lastcol).Interior.Color = 5296274
        End If
    End Sub
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

  3. #3
    Forum Contributor
    Join Date
    09-13-2008
    Location
    Los Angeles, CA
    MS-Off Ver
    365
    Posts
    120

    Re: Find Last Column with Data in Specific Row based on text

    Had to make a modification to the last row to get it to work how I wanted, but this is good, thank you!!!

    Range(Cells(fRow, 1), Cells(fRow, lastcol)).Interior.Color = 5296274

  4. #4
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MSO Home and Business 2024
    Posts
    7,363

    Re: Find Last Column with Data in Specific Row based on text

    You're welcome and thanks for rep+.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Find specific text within text and result dependent text in specified column
    By Eftychia in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-08-2019, 08:39 AM
  2. [SOLVED] VBA Macro to find text string in one column and replace specific text in another column if
    By bmahfood in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-21-2017, 11:15 AM
  3. excel 2007 macro to move data to a new column on the same row based on specific text
    By charmallan in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-15-2014, 06:29 PM
  4. [SOLVED] Search through columns and find specific text and replace that text with header column
    By adamzee in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-09-2014, 11:59 PM
  5. InStr function doesn't find specific text, but finds any text in column and runs code
    By mikey3580 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-27-2014, 03:50 AM
  6. [SOLVED] Find Specific Text in Text String and Return Value in Adjacent Column
    By watchouse in forum Excel General
    Replies: 2
    Last Post: 07-11-2012, 03:53 PM
  7. fastest way to find the row where a specific text is found in a specific column
    By getgray in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-23-2011, 02:45 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1