Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Find & replace up to certain row
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Sep 2018
    Posts
    132
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Find & replace up to certain row

    Is there a way for this code to only look until the last populated row in column A vs the entire columns D:K. IE, the data stops on row 84, but the following populates ' N/A' all the way down D:K where a cell is blank.

    Thanks in advance!


    Code:
        Columns("D:K").Select    Selection.Replace What:="", Replacement:="       N/A", lookat:=xlWhole, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,308
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Find & replace up to certain row

    Are those cells actually blank, or do they contain formulae that return ""
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular
    Join Date
    Sep 2018
    Posts
    132
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find & replace up to certain row

    They're blank cells that it is replacing with ' N/A'

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,308
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Find & replace up to certain row

    In that case, try
    Code:
    Sub srosk()
    Range("D:K").EntireColumn.SpecialCells(xlBlanks).Value = "       N/A"
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    Board Regular
    Join Date
    Sep 2018
    Posts
    132
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find & replace up to certain row

    Thanks.. can check soon. So if cell A86 has a value and D is blank, " N/A" will appear in D86.

    If A87 is blank, will D87 still be updated to " N/A" ? Ideally, it would remain blank (if possible). Not sure that logic is built in? Thanks again, as allways, Fluff!

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,308
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Find & replace up to certain row

    That will fill any blank cells in the used range for cols D:K with N/A
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    Board Regular
    Join Date
    Sep 2018
    Posts
    132
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find & replace up to certain row

    Is there a way for it to determine the row to stop on.. IE: stop at row 86 since row 87 onward is not used?

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,308
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Find & replace up to certain row

    It only acts on the UsedRange, so if row 86 is the last row in the used range, that is where it will stop.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #9
    Board Regular
    Join Date
    Sep 2018
    Posts
    132
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find & replace up to certain row

    Ok, my bad. I see what's happening. I copy 395 rows, do vlookups, and delete all rows where the vlookup result is #N/A, using a autofilter command. I think the UsedRange formula is still reading the blank rows 85-395 for some reason. I will try and figure out how to truly delete them.

    Another issue that I am having, is that it does not actually fill in the columns where a row has data. For instance... J6 is blank. E6:I6 have values. When I run your formula, " N/A" does not populate in J6. Any idea about this one? I tried the following, but it didn't yield different results.

    Code:
    Range("D:D").EntireColumn.SpecialCells(xlBlanks).Value = "       N/A"
    Range("E:E").EntireColumn.SpecialCells(xlBlanks).Value = "       N/A"
    Range("F:F").EntireColumn.SpecialCells(xlBlanks).Value = "       N/A"
    Range("G:G").EntireColumn.SpecialCells(xlBlanks).Value = "       N/A"
    Range("H:H").EntireColumn.SpecialCells(xlBlanks).Value = "       N/A"
    Range("I:I").EntireColumn.SpecialCells(xlBlanks).Value = "       N/A"
    Range("J:J").EntireColumn.SpecialCells(xlBlanks).Value = "       N/A"
    Range("K:K").EntireColumn.SpecialCells(xlBlanks).Value = "       N/A"
    Thanks as always!
    Last edited by srosk; May 8th, 2019 at 09:09 AM.

  10. #10
    Board Regular
    Join Date
    Sep 2018
    Posts
    132
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find & replace up to certain row

    So I think I got it... this fills in the N/A value, then deletes everything below where column A is blank

    Code:
        Columns("D:K").Select    Selection.Replace What:="", Replacement:="       N/A", lookat:=xlWhole, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
            
       Columns("A:A").Select
       Selection.SpecialCells(xlCellTypeBlanks).Select
       Selection.EntireRow.Delete

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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